Good Morning...

[Nigel Martin]

I want a cell to display "Good Morning" if it is between 00:00 and 12:00, display "Good Afternoon" if it between 12:00 and 17:00 and finally display "Good Evening" if it is between 17:00 and 00:00. - any ideas.

[Juan Pablo González]

When do you want to display this ? On opening ? on a cell (Formula ??) ? where ? when ? how ? (VBA / Formula) ?

[Nigel Martin]

I want to perform this task in a cell formula if possible e.g. =if(a1>17 etc.

[Mark W.]

="Good "&VLOOKUP(MOD(NOW(),1),{0,"Morning";0.5,"Afternoon";0.708333333333333,"Evening"},2)

[ This Message was edited by: Mark W. on 2002-03-28 12:42 ]

[Nigel Martin]

Many thanks that's exactly what I needed.
Regards
Nigel.

[Juan Pablo González]

Nigel, just in case it matters, the text will only update if there has been a cell calculation. Other wise it'll remain static.

[Joe Was]

Mark, slick use of the VLOOKUP array form.

Didn't know how it worked at first so I studied it. I never would have thought of the problem in that way, but I like it.

As far as I can tell your formula works like this:

VLOOKUP with array data returns the second element (Text) hence the 2 at the end of the formula. The general value of the MOD(NOW(),1) returns the NOW() remainder and VLOOKUP will use the largest value that is less than or equal to lookup_value.
So it returns the proper text for the current time bracket from
the array.

Once again nice work. JSW

[Mark W.]

On 2002-03-28 13:18, Juan Pablo G. wrote:
Nigel, just in case it matters, the text will only update if there has been a cell calculation. Other wise it'll remain static.

Actually, the NOW worksheet function is volatile... so the mere entry of a value will cause it's recalculation. In the absence of a volatile function the entry of a value into a cell without dependent cells wouldn't trigger recalculation.

[Juan Pablo González]

On 2002-03-28 15:20, Mark W. wrote:
Actually, the NOW worksheet function is volatile... so the mere entry of a value will cause it's recalculation. In the absence of a volatile function the entry of a value into a cell without dependent cells wouldn't trigger recalculation.

Mark, maybe I didn't explain myself clear... that's, sort of, what I was trying to say.

My point was, if you leave the sheet ALONE, it will not change the text automatically (That's also why i said, in case it matters...)

[Dave Hawley]

Couldn't you just use:

="Good " & IF(A1<0.5,"Morning",IF(A1<0.75,"Afternoon","Evening"))