Help - Time band seperator

[shumonsaha]

I have data in columns

Col A has program names A, B,C etc
Col B has time against each program

I want to insert col c so that

Programs between 02:00 to 05:59 is labelled late night, 06:00 to 09:59 is labelled morning, 10:00 to 13:59 is labelled Early Afternoon, 14:00 to 17:59 is labelled Afternoon, 18:00 - 21:59 is labelled Prime Time, 22:00 to 25:59 is labelled Late Prime

Can anyone give me a formula for this?

 

[jeffreybrown]

See how this does...

=IF(L2 < TIMEVALUE("05:59"),"Late Night",IF(L2 < TIMEVALUE("09:59"),"Morning",IF(L2 < TIMEVALUE("13:59"),"Early Afternoon",IF(L2 < TIMEVALUE("17:59"),"Afternoon",IF(L2 < TIMEVALUE("21:59"),"Prime Time",IF(L2 < TIMEVALUE("23:59"),"Late Prime",""))))))

 

[shumonsaha]

This works. But I get blank for the time between 23:59 and 25:59

All the other time bands are labelled correctly

 

[jeffreybrown]

How about at the end of the formula replace the "" with what you want for that time. BTW...what is 25:59?

 

[shumonsaha]

25:59 is 1.59 am

the data reports as 25:59

 

[shumonsaha]

I think removing " " with the value works

 

[jeffreybrown]

That's what I thought, but I would say we could not use 25:59 because in Excel it is not a time.

 

[shumonsaha]

Strange since I get 1:59 am as 25:59 in excel.

 

[jeffreybrown]

Yes I see...

 

[jeffreybrown]

Try...

=IF(L2 < TIMEVALUE("05:59"),"Late Night",IF(L2 < TIMEVALUE("09:59"),"Morning",IF(L2 < TIMEVALUE("13:59"),"Early Afternoon",IF(L2 < TIMEVALUE("17:59"),"Afternoon",IF(L2 < TIMEVALUE("21:59"),"Prime Time",IF(L2 < TIMEVALUE("25:59"),"Late Prime",""))))))