Greeting Function

[edwardpestian]

I have the following function. I'm trying to say that if it is before 12:00 pm, say "Good Morning," if after 12:00 pm but before 6:00 pm say "Good Afternoon," and otherwise say "Good Evening". The function however, is not working properly. Any ideas?

=IF(NOW()<TODAY()+HOUR(0.5),"Good Morning"&" "&"Today is"&" "&TEXT(TODAY(),"mmmm dd, yyyy"),IF(NOW()<TODAY()+HOUR(0.75),"Good Afternoon"&" "&"Today is"&" "&TEXT(TODAY(),"mmmm dd, yyyy"),"Good Evening"&" "&"Today is"&" "&TEXT(TODAY(),"mmmm dd, yyyy")))

Any ideas?

Thanks.

ep

[SteveG]

edward,

I typed in my cut off times in A1:A3 (12:00:00 AM, 12:00:00 PM, 6:00:00 PM respectively).

=IF(AND(TIMEVALUE(TEXT(NOW(),"hh:mm:ss"))>=A1,TIMEVALUE(TEXT(NOW(),"hh:mm:ss"))<A2),"Good Morning",IF(AND(TIMEVALUE(TEXT(NOW(),"hh:mm:ss"))>=A2,TIMEVALUE(TEXT(NOW(),"hh:mm:ss"))<A3),"Good Afternoon","Good Evening"))

HTH

Steve

[Heather Heritage]

surely

=if(now()-int(now())<.5,"Good Morning",if(now()-int(now())<.75,"Good
Afternoon","Good Evening"))

is simpler?!


"SteveG" <[email protected]> wrote in
message news:[email protected]...
>
> edward,
>
> I typed in my cut off times in A1:A3 (12:00:00 AM, 12:00:00 PM, 6:00:00
> PM respectively).
>
>
=IF(AND(TIMEVALUE(TEXT(NOW(),"hh:mm:ss"))>=A1,TIMEVALUE(TEXT(NOW(),"hh:mm:ss
"))<A2),"Good
>
Morning",IF(AND(TIMEVALUE(TEXT(NOW(),"hh:mm:ss"))>=A2,TIMEVALUE(TEXT(NOW(),"
hh:mm:ss"))<A3),"Good
> Afternoon","Good Evening"))
>
> HTH
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=555043
>

[edwardpestian]

Thanks Heather and Steve. Both options worked great!

[SteveG]

You're welcome.

Thanks for the feedback,

Steve

[tim m]

One thing to remember, unless I am mistaken the time from the NOW function
will only be updated in a cell when a recalculation is done. Will this
affect the formulas that a NOW is part of?

(As in if you just use a NOW formula it will list a time and date, that time
and date will remain as is unless you recalc to match with time passing on
your clock.)

"edwardpestian" wrote:

>
> I have the following function. I'm trying to say that if it is before
> 12:00 pm, say "Good Morning," if after 12:00 pm but before 6:00 pm say
> "Good Afternoon," and otherwise say "Good Evening". The function
> however, is not working properly. Any ideas?
>
> =IF(NOW()<TODAY()+HOUR(0.5),"Good Morning"&" "&"Today is"&"
> "&TEXT(TODAY(),"mmmm dd, yyyy"),IF(NOW()<TODAY()+HOUR(0.75),"Good
> Afternoon"&" "&"Today is"&" "&TEXT(TODAY(),"mmmm dd, yyyy"),"Good
> Evening"&" "&"Today is"&" "&TEXT(TODAY(),"mmmm dd, yyyy")))
>
> Any ideas?
>
> Thanks.
>
> ep
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=555043
>
>

[edwardpestian]

Yes, that is correct. TODAY() and NOW() functions are volatile. They will update when any formula in the entire workbook recalculates regardless of cell references, or when the workbook is opened or saved. However, they remain static until excel calculates.

ep

[Biff]

even simpler:

A1 = =NOW()

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

Biff

"Heather Heritage" <[email protected]> wrote in
message news:[email protected]...
> surely
>
> =if(now()-int(now())<.5,"Good Morning",if(now()-int(now())<.75,"Good
> Afternoon","Good Evening"))
>
> is simpler?!
>
>
> "SteveG" <[email protected]> wrote in
> message news:[email protected]...
>>
>> edward,
>>
>> I typed in my cut off times in A1:A3 (12:00:00 AM, 12:00:00 PM, 6:00:00
>> PM respectively).
>>
>>
> =IF(AND(TIMEVALUE(TEXT(NOW(),"hh:mm:ss"))>=A1,TIMEVALUE(TEXT(NOW(),"hh:mm:ss
> "))<A2),"Good
>>
> Morning",IF(AND(TIMEVALUE(TEXT(NOW(),"hh:mm:ss"))>=A2,TIMEVALUE(TEXT(NOW(),"
> hh:mm:ss"))<A3),"Good
>> Afternoon","Good Evening"))
>>
>> HTH
>>
>> Steve
>>
>>
>> --
>> SteveG
>> ------------------------------------------------------------------------
>> SteveG's Profile:
> http://www.excelforum.com/member.php...fo&userid=7571
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=555043
>>
>
>

[rajeev.raj]

Hello Experts,
the above formula is not working for "Good Evening"

[FDibbins]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread