How to detect time between 18:00 and 06:00?

**drx** · 10-17-2011, 05:11 PM

Hi,
I'm trying to write an if statement to show "day" if it is between 06:00 and 18:00 and "night" if it is between 18:00 and 06:00.

This is harder for me than perhaps it should be! Any ideas?

**JBeaucaire** · 10-17-2011, 05:12 PM

Try this:

=IF(OR(A1>0.75,A1<0.25), "Night", "Day")

0.75 is the decimal equivalent of the time formatted as "18:00".
0.25 is the decimal equivalent of the time formatted as "6:00".

**drx** · 10-17-2011, 05:19 PM

This works perfectly, but...I was hoping to combine it with a cell containing the now() function, but for some reason it doesn't like that - even when the cell is formatted as hh:mm.

Any ideas?

**EDIT** Fixed this myself, wrapped now() in a mod(now(),1) and it worked. Thanks!

Originally Posted by JBeaucaire

Try this:

=IF(OR(A1>0.75,A1<0.25), "Night", "Day")

0.75 is the decimal equivalent of the time formatted as "18:00".
0.25 is the decimal equivalent of the time formatted as "6:00".

**JBeaucaire** · 10-17-2011, 05:51 PM

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**daddylonglegs** · 10-17-2011, 06:12 PM

Originally Posted by drx

This works perfectly, but...I was hoping to combine it with a cell containing the now() function, but for some reason it doesn't like that - even when the cell is formatted as hh:mm.

For NOW()....

=LOOKUP(HOUR(NOW()),{0,6,18,"Night","Day","Night"})

or

=IF(MATCH(HOUR(NOW()),{0,6,18})=2,"Day","Night")