ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] Â^¨úÀx¦s®æ¤é´Á°ÝÃD

[µo°Ý] Â^¨úÀx¦s®æ¤é´Á°ÝÃD


»¡©ú¡G
BÄ椽¦¡¡A¥D­n­pºâAÄæ¤é´Á¬O§_¤p©óDÄæ¡A¦p¤p©ó«hBÄæÅã¥Ü"¹O´Á"
¦ý¦]¬°AÄæ³æ¤@Àx¦s®æ¦³¥i¯à·|¦³2­Ó¥H¤W¤é´Á¤£¤@©w(¨Ò¦pA2:A6)¡A¦ý¥u»Ý°Ñ¦Ò³Ì¤W­±²Ä¤@­Ó¤é´Á¬°¥D§Y¥i¡A¬G·s¼W¤FCÄ滲§UÄæ
CÄæ¥D­n¬O§ì¨úAÄæ "-" ¥H«eªº¤é´Á¡A´£¨Ñµ¹BÄ椽¦¡°Ñ·Ó
DÄæ«h¬O=TODAY()¤½¦¡»²§UÄæ


°ÝÃD¡G
CÄ椽¦¡¦p¦óª½±µ¦X¨Ö¶iBÄ椽¦¡¤º¡A¬G¦X¨Ö«h§Æ±æ¥i§R°£CÄ滲§UÄæ
¥t¥~§Úµo²{¥Ø«eCÄ椽¦¡¥u­n¹J¨ìAÄ榳2­Ó¥H¤W¤é´Á(¨Ò¦pA2:A6)¡A¤é´Á§Y«K¤p©óDÄæ¡ABÄ椴¤£·|¥X²{"¹O´Á"¡A¤£½T©w¬O­þ¸Ì¥X¤F°ÝÃD


¥H¤W¨D¸Ñ~~~~~~«D±`·PÁÂ!!!!



´ú¸ÕÀÉ.rar (10.89 KB)
*¦v¤k¤@ªTµL»~*

¦^´_ 1# msmplay
B2=IF(ISERR(FIND("-",A2))*(A2>D2),"",IF(ISERR(FIND("-",A2))*(A2<D2),"¹O´Á",IF(FIND("-",A2),IF(DATEVALUE(MID(A2,1,FIND("-",A2,1)-2))>D2,"","¹O´Á"))))
¦V¤U½Æ»s
½Ð°Ñ¦Ò¡I

TOP

¥»©«³Ì«á¥Ñ KCC ©ó 2016-7-17 13:22 ½s¿è

¦^´_ 1# msmplay

    °Ñ¦Ò§A­ì¨Ó¤½¦¡¦C¥X¥i¯à­nªºÀË´úÂI¡A¥i¦Û¤vµø»Ý­n½Õ¾ã¤½¦¡

    B2=IF((A2="")+(A2="-"),"",IF((IF(ISERR(FIND("-",A2)),A2,--(LEFT(A2,FIND("-",A2)-2)))<D2),"¹O´Á",""))  ¤U©Ô

     1.find("-",A1), §ä¨ì"-"ªº¦ì¸m«á¡A¦]¬°«e­±ÁÙ¦³¤@­Ó´«¦æ¦r¤¸(char(10), «ö alt-enter«á²£¥Í)¡A©Ò¥H­n´î 2
        ­Y§ï¦¨find char(10)¡A´N­n§ï¦¨´î1¡C
     2.¦P1­ì²z¡A ¤£´î2ªº¸ÜºI¨ú¥X¨Óªº¬O¦r¦ê¡A¦r¦ê©M¤é´Á¤ñ¤j¤p¡A¦r¦ê>¼Æ¦r¡A©Ò¥H¤£·|¹O´Á¡C
         ¦Û¤v¥Îtype()©Mlen()ÀË´ú¤@¤U§AºI¨ú¥X¨Óªº¸ê®Æ«¬ºA©Mªø«×´NÀ´¤F¡C
     3.¥Îiserr©Misnumber³£¥i¥H¡AÅÞ¿è³B²z­è¦n¬Û¤Ï¡Aif¤½¦¡¹ï½Õ½Õ¾ã¤@¤U´N¥i¡C

TOP

=IF(COUNT(0/(-LOOKUP(,-LEFT(A2,{8,9,10}))<D2)),"¹O´Á","")

LEFT(A2,{8,9,10})
¤é´Á®æ¦¡¥i¯à¦p¤U:
2016/3/1   8­Ó¦r¤¸
2016/3/12  9­Ó¦r¤¸
2016/12/1  9­Ó¦r¤¸
2016/10/23  10­Ó¦r¤¸

¦P®É¥H°}¦C¨ú{8,9,10}¦ì¤¸, ¦A¥Hlookup±a¥X³Ì§¹¾ã¤é´Á¨Ó¤ñ¹ï§Y¥i!

TOP

¦^´_ 5# ­ã´£³¡ªL

­ã¤j~~¦n¼F®`£¬¡I
*¦v¤k¤@ªTµL»~*

TOP

¬°¤°»ò¿é¤J=-LOOKUP(,-LEFT(E2,{8,9,10}))
·|¬O¥X²{2016/7/15
³o¦n±j
¦ý¬Ý¤£À´
¨D­ã¤j¸ÑÄÀ

TOP

³o¼Ë¤]·|¦³¤@¼Ë®ÄªGÕÙ
=IF(-LOOKUP(,-LEFT(A2,{8,9,10}))<D2,"¹O´Á","")

TOP

¦^´_ 6# popomilk


=LEFT(A2,{8,9,10})¡@¡Ö²£¥Í³o¤TºØµ²ªG¡@{"2016/7/1","2016/7/15","2016/7/15?"}¡@¬õ¦â¡Hªí¥Ü¡e´«¦æ¦r¤¸¡f
=-LEFT(A2,{8,9,10})¡@¡ÖÅܦ¨³o¤TºØµ²ªG¡@{-42552,-42566,#VALUE!}¡@¦³®Ä¤é´Á§Ç¦C­È¥u³Ñ¢±­Ó
=-LOOKUP(,-LEFT(A2,{8,9,10}))¡@¡×¡@42566¡A¥ç§Y¬° 2016/7/15¡@¡]LOOKUP¥u§ì³Ì«á¤@­Ó¦³®Ä¼Æ­È¡^

TOP

¦^´_ 7# popomilk


­Y¢ÏÄæ³£¦s¦b¤é´Á¡A³o¤½¦¡¥i¥H¡I
¦ý­Y¬°ªÅ®æ©Î«D¤é´Á¡A·|²£¥Í¿ù»~­È¡I

TOP

B2=IF(--LEFT(A2,FIND(CHAR(10),A2&CHAR(10))-1)<D2,"¹O´Á","")
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD