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

[µo°Ý] «e´X¤Ñ¶]¤F¤@¦¸¡A¤Sµo²{·sªº°ÝÃD¤F

[µo°Ý] «e´X¤Ñ¶]¤F¤@¦¸¡A¤Sµo²{·sªº°ÝÃD¤F

¦U¦ì«e½ú±z¦n;¦n´X¤Ñ¨S¤W¨Ó¤F¡A¤p¤kªº¤ü¤l¦í°|¤F¡A¨S®É¶¡¤W¨Ó¡A¥¿¦n¤µ¤Ñ¦³±aµ§°O«¬¹q¸£¨Ó¡A¤£µM·|«ÜµL²á¡A
«e´X¤Ñ¶]¤F¤@¦¸¡A¤Sµo²{·sªº°ÝÃD¤F
¤@¡B¦­°h(30¤À­p)=IF(C5<6,CEILING(MAX(0,$P$1-$E5),1/24/2)*24*2,"0")      
¦p¦ó¸I¨ì°ê©w°²¤é©M¬P´Á¤»¬P´Á¤é¡A­ÈÅã¥Ü¬° 0 ©O?
¤G¡B°²¤é¥[¯Z(30¤À­p)   =IF(C5>5,MAX(0,FLOOR(MAX(0,E5-$L$1),1/24/2)*24*2),"0")   §@¥X¨Óªº­È¬O18¡A
¦ý¤@¤Ñ¤W¯Z¤¤¤È¦³¥ð¤@¤p®É­n¦p¦ó¥h­×§ï¥¦¡A­È·|¬O16¡A§Ú§â¥¦§ï¦¨
=IF(C5>5,MAX(0,FLOOR(MAX(0,E5-$L$1),1/24/2)*24*2),"0")-2  ¦ý¬O¡A¤£¬O°²¤éªº­È·|Åܦ¨-2¡A
­þ¬O§_¥i¥H¦b¥[¤W°ê©w°²¤é¥[¯Zªº­È©O?
¤T¡B(¦Ò¶Ô²Î­pªí)   ½Ð°²¤Ñ¼Æ  =COUNTBLANK(¦Ò¶Ôªí!D5:E35)/2-10  
¦ý¬O¦pªG­û¤u³o­Ó¤ë½Ð°²¤@¤Ñ¤S°²¤é¥[¯Z¤@¤Ñ ¡A½Ð°²¤Ñ¼Æ ­È·|¬O0¤]¡A¦]¸Ó¬O1¤~¹ï
¥|¡B(­û¤u¸ê®Æ)  ¦~ÄÖ=DATEDIF(E2,TODAY(),"Y")&"·³"&DATEDIF(E2,TODAY(),"ym")&"­Ó¤ë"  ¦pªGE2¨S¦³¿é¤J­È¦p¦óÅã¥Ü¬°ªÅ¥Õªº©O?
¹ï¤£°_O¡A¤@¤W¨Ó´N°Ý¦n´X­Ó°ÝÃD

­û¤u¦Ò¶Ô0419.zip (49.68 KB)

celina710828

¥»©«³Ì«á¥Ñ gong ©ó 2011-4-28 22:11 ½s¿è

°²¤é¥[¯Z(30¤À­p)§Ú§â¥¦¥[¤W°ê©w°²¤é¥i¬O¤£¦æ¬O­þ¸Ì¥X°ÝÃD¤F.
=IF($C5>5)+COUNTIF(°ê©w°²¤é!$A:$A,$A5),0,CEILING(MAX(0,FLOOR(MAX(0,$E5-$L$1),1/24/2)*24*2-2),0)

·PÁÂG¤j¡A§Ú°Ñ¦Ò¨ä¥¦Àx¦s®æ¥[¤W°ê©w°²¤é¡A¤w§¹¦¨¤F  °²¤é¥[¯Zªº³¡¥÷¡AÁÂÁÂ!:lol
=IF(A5="","",IF(($C5>5)+COUNTIF(°ê©w°²¤é!$A:$A,$A5),MAX(0,FLOOR(MAX(0,$E5-D5),1/24/2)*24*2-2),0))
celina710828

TOP

¦Û¤v¦A§V¤O¤@ÃD´N¥i¥H¤É¯Å¤F
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

G¤j¬O§_¥i¥H¶Çmsnµ¹§Ú©O¡A§ÚµLªk¤U¸ü
celina710828

TOP

G¤j·PÁ±zªº¤À¨É¡A³£§ÚÁÙ¬O­Ó¤p¾Ç¥Í¡AµLªk¤U¸ü¤]¡AÁÙ¦³§Ú¤w§ï¦n¦~ÄÖ¤F¡A­ì¨Ó¬O¤Ö¤F  &  ªº²Å¸¹O
celina710828

TOP

¥»©«³Ì«á¥Ñ gong ©ó 2011-4-25 23:16 ½s¿è

=IF(E2="","",DATEDIF(E2,TODAY(),"Y")&"·³"&DATEDIF(E2,TODAY(),"ym")&"­Ó¤ë")

¨ä¥Lªº­×§ï¦bªþ¥ó¤¤½Ð°Ñ¦Ò

Book1.rar (34.33 KB)

ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

«¢«¢«¢«ç»ò¶]¥X­þ»ò¦hªí±¡Y
celina710828

TOP

¥»©«³Ì«á¥Ñ gong ©ó 2011-4-25 22:54 ½s¿è

°²¤é¥[¯Z(30¤À­p)§Ú§â¥¦¥[¤W°ê©w°²¤é¥i¬O¤£¦æ¬O­þ¸Ì¥X°ÝÃD¤F.
=IF($C5>5)+COUNTIF(°ê©w°²¤é!$A:$A,$A5),0,CEILING(MAX(0,FLOOR(MAX(0,$E5-$L$1),1/24/2)*24*2-2),0)


(­û¤u¸ê®Æ)  ¦~ÄÖf2=if(e2="","",DATEDIF(E2,TODAY(),"Y")"·³"&DATEDIF(E2,TODAY(),"ym"))&"­Ó¤ë"
µo¥Í¿ù»~

½Ð°²¤Ñ¼Æ
=NETWORKDAYS(A5,A34,°ê©w°²¤é!A2:A14)-SUMPRODUCT((C5:C34<6)*(D5:D34<>""))+SUMPRODUCT((C5:C34<6)*(D5:D34<>"")*COUNTIF(°ê©w°²¤é!$A$2:$A$14,$A$5:$A$34))
µo¥Í¿ù»~


¦­°h(30¤À­p)   
¦p¦ó¸I¨ì°ê©w°²¤é©M¬P´Á¤»¬P´Á¤é¡A­ÈÅã¥Ü¬° 0 ©O?
g5=IF(($C5>=6)+COUNTIF(°ê©w°²¤é!$A:$A,$A5),0,CEILING(MAX(0,$P$1-$E5),1/24/2)*24*2)
¦­°h¸I¨ì°ê©w°²¤é¬O¥i¥H¤F¦ý¥­¤é½Ð°²·|¥X²{34ªº­È
I am sorry:( µ¹¤j¤j±z¶K³Â·Ð¤F)
celina710828

TOP

¥|¡B(­û¤u¸ê®Æ)  ¦~ÄÖ=DATEDIF(E2,TODAY(),"Y")"·³"&DATEDIF(E2,TODAY(),"ym")&"­Ó¤ë"  ¦pªGE2¨S¦³¿é¤J­È¦p¦óÅã¥Ü¬°ªÅ¥Õªº©O?

f2=if(e2="","",DATEDIF(E2,TODAY(),"Y")"·³"&DATEDIF(E2,TODAY(),"ym"))&"­Ó¤ë"
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¥»©«³Ì«á¥Ñ gong ©ó 2011-4-25 14:18 ½s¿è

¤T¡B(¦Ò¶Ô²Î­pªí)   ½Ð°²¤Ñ¼Æ  =COUNTBLANK(¦Ò¶Ôªí!D5:E35)/2-10  
¦ý¬O¦pªG­û¤u³o­Ó¤ë½Ð°²¤@¤Ñ¤S°²¤é¥[¯Z¤@¤Ñ ¡A½Ð°²¤Ñ¼Æ ­È·|¬O0¤]¡A¦]¸Ó¬O1¤~¹ï

½Ð°²¤Ñ¼Æ
=NETWORKDAYS(A5,A34,°ê©w°²¤é!A2:A14)-20+SUMPRODUCT((C5:C34<6)*(D5:D34<>"")*COUNTIF(°ê©w°²¤é!$A$2:$A$14,$A$5:$A$34))

§ó¥¿
=NETWORKDAYS(A5,A34,°ê©w°²¤é!A2:A14)-SUMPRODUCT((C5:C34<6)*(D5:D34<>""))+SUMPRODUCT((C5:C34<6)*(D5:D34<>"")*COUNTIF(°ê©w°²¤é!$A$2:$A$14,$A$5:$A$34))

³o­Ó´N¬O²Ê¤ßªºµ²ªG
f9«ö§¹«áª½±µ´N¶K¤W¨Ó¾É­P¬Ý¤£À´
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

        ÀR«ä¦Û¦b : ÀR§¤±`®¦¤v¹L¡B¶¢½Í²ö½×¤H«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD