IF Function with multiple outcomes???

[hotdogs1999]

Hello all,


new to this forum, and pretty new to excel, so here goes my first question.


i have one column that has dates in it for 'Follow Up' alerts. have made a formula so if the date is between a certain range then a message shows in the next cell.


my formula currently is =IF((AND(J24=(TODAY()+1),J24<(TODAY()+3))),"UPDATE PENDING","")


this is only doing 1 part of what i need though.


what i'm after is a formula that gives the following:
- if between 1&3 days from now - display "UPDATE PENDING" - which i have done in the above formula
- if today - display "UPDATE TODAY"
- if <today (past) - "OVERDUE"


i can then conditionally format them to change colour depending on what the message is.


would much appreciate some help on this


thanks in advance

 

[hotdogs1999]

sorry, i missed the 3rd 'if'

- if past (yesterday backwards etc.) - "OVERDUE"

cheers

 

[Fluff]

how about
=IF(J24< TODAY(),"overdue",IF(J24=TODAY(),"update today",IF(J24< TODAY()+3,"update pending","")))

 

[hotdogs1999]

that works great. thanks.

however it is displaying "OVERDUE" on blank cells that don't have a value. can this be fixed?

 

[Fluff]

How about
=IF(J24="","",IF(J24< TODAY(),"overdue",IF(J24=TODAY(),"update today",IF(J24< TODAY()+3,"update pending",""))))

 

[hotdogs1999]

excellent. thank you.

works great.

 

[Fluff]

You're welcome & thanks for the feedback

 

[hotdogs1999]

sorry to be annoying.

just went to conditional format them to change colour and won't work now!!

was setting it up to change colour when it had the certain phrases.

any idea why this wouldn't be working? any suggestions for fixing it?

 

[Fluff]

Do you mean that the formula is not working, or the CF doesn't work?

 

[hotdogs1999]

formulas fine.

having trouble with CF