Display test string depending on date

**Badvgood** · 03-31-2009, 07:30 AM

Hello all,

I need a forumla that will dispay a set text message in a cell based on the date in another cell.

I need cell A1 to display the text message "Forwards Booking Date, No Action Required" Where cell A2 has a date entered in it that is the same as todays date or greater - i.e. in the future.

And if the date is in the past for no text message to appear.

Thanks in advance,

BVG

**zbor** · 03-31-2009, 07:36 AM

=IF(E1<TODAY();"";"Forwards Booking Date, No Action Required")

**DonkeyOte** · 03-31-2009, 07:38 AM

zbor, I think that should be <TODAY() rather than <NOW() .. the latter includes a time stamp so if E1 contained 31/03/2009 using NOW() would result in Null given NOW() > TODAY() ... say it were noon NOW() = 39903.5 whereas TODAY = 39903

**zbor** · 03-31-2009, 07:48 AM

True... Corrected..

But also, this might be usefull:

=IF(E1>=TODAY();"No action required. Expire in: "& E1-TODAY() &" days";"")

**Badvgood** · 03-31-2009, 08:15 AM

Using =IF(E1<TODAY();"";"Forwards Booking Date, No Action Required")

Does not seem to work, Excel tells me the formula I entered contains an error.

If I replaced the ; with , it lets me enter it but still fails to give the result I am looking for.

Any ideas?

**DonkeyOte** · 03-31-2009, 08:20 AM

You need to reference A2 per your original question I believe:

=IF(A2<TODAY(),"","Forwards Booking Date, No Action Required")

**Badvgood** · 03-31-2009, 08:29 AM

Done that, its J5.

Attached for all to see now.

The formula needs to go in L5

**DonkeyOte** · 03-31-2009, 08:35 AM

From what I can see...

L5: =IF(ISERR(0+J5),"",IF(0+J5<TODAY(),"","Forwards Booking Date, No Action Required"))

Also you have Lotus Notes Transition Formula Entry activated - not sure if required or not http://support.microsoft.com/kb/213953

**Badvgood** · 03-31-2009, 08:58 AM

Hi,

Thanks for your patience, but its still not working for me....

I have entered the formula provided and copie dit down to cell L325 but none of the results I would have liked are displayed!

For example, in L5 I would expect it to be blank - Which it is.

But in L7 where the formula is looking at a date of April 1st 2009 I would expect to see the text message "Future booking date, no action required" - This does not happen.

Any ideas as to how to solve?

Maybe you can make it work and upoad the spread sheet?

**zbor** · 03-31-2009, 03:28 PM

now it's working...

Replace ; with , if you need...

=IF(ISERR(DATEVALUE(J5));"";IF(DATEVALUE(J5)<TODAY();"";"Forwards Booking Date, No Action Required"))