same time range on different days

[tmax]

If I have data sets as below (up to 1000 on daily basis), how can I set a
formula that returns:
"breakfast" if the time is between 7:00 and 8:30
"lunch" if the time is between 11:30 and 14:00
"dinner" if the time is between 17:00 and 20:00
and
"midnight" if the time is between 23:00 and 1:00

the date is not important and should not be regarded, what is important is
the time period.


12/1/08 9:53
12/2/08 9:28
12/2/08 14:49
12/2/08 20:29
12/4/08 9:30
12/4/08 13:21
12/4/08 13:21
12/4/08 13:21
12/5/08 9:13


Thank you very much.

 

[Jarek Kujawa]

try:

=IF(AND(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>TIME(7,30,),TIME(HOUR
(A1),MINUTE(A1),SECOND(A1))<TIME(8,30,)),"breakfast",IF(AND(TIME(HOUR
(A1),MINUTE(A1),SECOND(A1))>TIME(11,30,),TIME(HOUR(A1),MINUTE
(A1),SECOND(A1))<TIME(14,0,)),"dinner",IF(AND(TIME(HOUR(A1),MINUTE
(A1),SECOND(A1))>TIME(17,0,),TIME(HOUR(A1),MINUTE(A1),SECOND(A1))<TIME
(20,0,)),"dinner",IF(OR(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>TIME
(23,0,),TIME(HOUR(A1),MINUTE(A1),SECOND(A1))<TIME
(1,0,)),"midnight",""))))

 

[David Biddulph]

Perhaps you could clarify something for us, please, Jarek?

Why TIME(HOUR(A1),MINUTE(A1),SECOND(A1)) instead of just MOD(A1,1) ?

 

[Jarek Kujawa]

wow, Dave, you're following my posts, again
thanks for expressing yr doubts
nothing to add, doesn't my formula work on your PC?
;-)
HIH
pls click "YES" if my post was helpful
;-)

 

[Jarek Kujawa]

MOD(A1,1)?
what an excellent formula it is...
;-)

 

[barry houdini]

Assuming that any time outside those periods should give a blank try

=IF(A1="","",LOOKUP(MOD(A1,1)*24,{0,1,7,8.5,11.5,14,17,20,23},
{"midnight","","breakfast","","lunch","","dinner","","midnight"}))

 

[tmax]

Thank you guys very much. Both functions work wonderfully.

I just learned quite a bit about excel

Happy new year!