Change value of cell based on time of day

[andrewb90]

Hello all,

I wasn't sure if this could be done as a formula, but basically, I am looking for something that would return the cell value of "Good morning" from 1:00-12:00, good afternoon from 12:01-17:00, and good evening from 17:01 to 01:00. is there a simple way to do this?

The reasoning behind this is that I have a user form that will greet the user, and the first line is going to be a cell reference, which will hopefully display the appropriate message based on time of day. Although any other creative solutions out there are certainly welcome!

Thanks!

 

[Scott T]

Try

=VLOOKUP(MOD(NOW(),2),{0,"Good Evening";0.0416666666666667,"Good Moring";0.500694444444444,"Good Afernoon";0.709027777777778,"Good Evening"},2)

 

[andrewb90]

awesome, thank you!

 

[Scott T]

Glad to help

 

[andrewb90]

Are the decimals just the numerical equivalent to time? so 6 AM is .25, noon is .5, etc?

 

[Scott T]

Yes, it is the part of a 24 hour day so 6AM/24=.25 and 13(1PM)/24=.54166. To the left of the decimal would be the date so 42998.54166 would be 9/20/17 1PM.

 

[FormR]

=VLOOKUP(MOD(NOW(),[COLOR=#ff0000][B]2[/B][/COLOR]),{0,"Good Evening";0.0416666666666667,"Good Moring";0.500694444444444,"Good Afernoon";0.709027777777778,"Good Evening"},2)

Hi, this should be MOD 1

Here is another option that might be a little more intuitive:

="Good "&LOOKUP(MOD(NOW(),1),0+{"00:00","01:00","12:01","17:01"},{"Evening","Morning","Afternoon","Evening"})