p212

¦^´_ 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

p212

¦^´_ 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

p212

¦^´_ 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

p212

¦^´_ 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

p212

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