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

¥[¯Z¨ç¼Æ©Ç©Çªº¡H¡H¡H

¥[¯Z¨ç¼Æ©Ç©Çªº¡H¡H¡H

QÄæ¦ì¯»¬õµù°O¬O®M¥Î¨ç¼Æ¿ù»~¡I
¤½¦¡¤@¡G=FLOOR(MAX((I2-"17:30")*24);0.5;1)
QÄæ¦ì¦pªG´£¦­¤U¯Z¡A¥[¯Z®É¼Æ¥X²{23h¡I¡I
¤½¦¡¤G¡G=HOUR(I2-"17:30")+IF((MINUTE(I2-"17:30"))>=30;0.5;0)
­nªº»Ý¨D¦p¤U¡G
¤U¯Z®É¶¡17:30
17:30~18:00¤£ºâ¥[¯Z
18:00¶}©l¥¿¦¡¥[¯Z
17:30-18:30  ¥[¯Z1h
17:30-19:00  ¥[¯Z1.5h
17:30-19:30  ¥[¯Z2h
17:30-20:00  ¥[¯Z2.5h
17:30-20:30  ¥[¯Z3h
17:30-21:00  ¥[¯Z3.5h
17:30-21:30  ¥[¯Z4h
17:30-22:00  ¥[¯Z4.5h
17:30-22:30  ¥[¯Z5h
17:30-23:00  ¥[¯Z5.5h
17:30-23:30  ¥[¯Z6h
½Ð¦U¦ì°ª¤â­ÌÀ°­Ó¦£¡IÁÂÁ¡I

¥[¯Z.xls.zip (4.6 KB)

Anita

¦^´_ 1# ®}¤p¦p
Àx¦s®æQ1¿é¤J
=IF(I2>=TIMEVALUE("18:30:00"),FLOOR((I2-TIMEVALUE("17:30:00"))*24,0.5),0)
¦V¤U½Æ»s
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 2# p212



ÁÂÁ§AªºÀ°¦£¸Ñµª¡IÁÂÁ¡I
¤£¹L¦³¤@­Ó°ÝÃD¡A¦pªG®É¶¡21:22¬O¥[¯Z3.5h¡A§Ú­nºâ¦¨¥[¯Z4h¡]¦C¬°21:30¡^¡A¤½¦¡¦p¦ó®M¥Î¡I
Anita

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-12-8 11:49 ½s¿è

¦^´_ 3# ®}¤p¦p
¥[¯Z¤£¨¬¥b¤p®ÉªÌ¡A±zªº»{¦C(®e§Ô)½d³ò¬°¦ó¡H
­Y¬OµL±ø¥ó¶i¦ì¡A«h¥i¦Ò¼{±NÀx¦s®æQ2¤½¦¡§ï¥Î
=IF(I2>=TIMEVALUE("18:30:00"),CEILING((I2-TIMEVALUE("17:30:00"))*24,0.5),0)
¦V¤U½Æ»s
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 4# p212

¤w¸g¦³¤£¨¬¶i¦ì¤F¡I¦ý21:15¤§«e¤]¸òµÛ¶i¦ì¤F¡I­n¦p¦ó¤U¹F«ü¥O¡I¥H21:15¤§«á¶i¦ì¡H
ÁÂÁ¡I
Anita

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-12-8 14:32 ½s¿è

¦^´_ 5# ®}¤p¦p
½Ð°Ý±zªº·N«ä¬O­n¡u¥[¯Z¶W¹L15¤ÀÄÁ(i.e. >15min)¥H0.5¤p®É­p¡B¥[¯Z¶W¹L45¤ÀÄÁ(i.e. >45min)¥H1¤p®É­p¡v¡H

TOP

¦^´_ 6# p212


    ¬Oªº¡I
Anita

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-12-9 14:57 ½s¿è

¦^´_ 7# ®}¤p¦p
©ó¡u¥[¯Z¶W¹L15¤ÀÄÁ(i.e. >15min)¥H0.5¤p®É­p¡B¥[¯Z¶W¹L45¤ÀÄÁ(i.e. >45min)¥H1¤p®É­p¡v±ø¥ó¤U
Àx¦s®æQ2¿é¤J
=IF(I2<TIMEVALUE("18:30:00"),0,IF(MINUTE(I2)<=15,(TIME(HOUR(I2),,)-TIMEVALUE("17:30:00"))*24,IF((MINUTE(I2)>45),(TIME(HOUR(I2)+1,,)-TIMEVALUE("17:30:00"))*24,(TIME(HOUR(I2),30,)-TIMEVALUE("17:30:00"))*24)))
¦V¤U½Æ»s
½Ð°Ñ¦Ò¡I
ª`¡G¤U¯Z®É¶¡¬°17:30¡A¦Ó17:30~18:00¤£ºâ¥[¯Z¡A¬G¤W¦¡¡u IF(I2<TIMEVALUE("18:30:00"),0,¡v¡C

TOP

¦^´_ 8# p212


    ¤£¦n·N«ä¡I§Ú²{¦b®M¥Î¨ì¨ìMAC¹q¸£¡I¤½¦¡®M¥Î¤W¥hÅã¥Ü¿ù»~¡G508¡I
­n¦p¦ó¸Ñ¨M¡IÁÂÁ¡I
Anita

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-12-10 12:02 ½s¿è

¦^´_ 9# ®}¤p¦p
¥ÎOffice: mac 2011´ú¸Õµ²ªG¤@¤Á¥¿±`

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD