Conditional formatting on dates

Hi,

I want to use conditional formatting in an excel document to highlight:

Out of date dates - red
Will expire next calendar month (or in 30 days) - orange
In date - green

I have managed the red and green but the orange doesn't seem to work no what order I put the rules in.

I currently have my dates in format dd-yyyy as a specific date is not really relevant to my data

Any help greatly appreciated!

Many Thanks
Leigh MacpheeAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
See attached with updated rules:

Green:   =AND(I4<>"",I4>TODAY()+30)
Amber:  =AND(I4<>"",I4>TODAY(),I4<TODAY()+30)
Red:      =AND(I4<>"",I4<=TODAY())
Applies to range:   =$I$4:$I$50    for all of them, adjust the 50 as required.

Also adjusted the Applies to Range on the rule on column J to $J$4:$J$50 as this was piecemeal and got rid of all the other attempts.
SHE-draft.xlsx
0
 
KoenConnect With a Mentor Change and Transition ManagerCommented:
look at attached sheet if this is what you need...
dateformatting.xlsx
0
 
AutomotionConnect With a Mentor Commented:
What are you using as your formulas?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rob HensonConnect With a Mentor Finance AnalystCommented:
With a sample file we can apply some rules for you.

As a starter though you need to think of dates as numbers as that is how Excel stores them.

The function =TODAY() will return the current date.

Therefore, with a date in A1:
A1>TODAY() is TRUE if in the future
A1<TODAY() is TRUE if in the past
AND(A1>TODAY(),A1<TODAY()+30)  is TRUE if within the next 30 days
0
 
Leigh MacpheeAuthor Commented:
Thanks for all your replies.

Koen I have tried to apply your rules but it only seems to be picking up the orange and green values (apologies if I'm just being really dumb).

I have attached a sample file.

To try and explain, I am setting up a database which will contain links to a whole load of documents; these documents will need updating periodically.

I have formula '=DATE(YEAR(C4) + 1, MONTH(C4), DAY(C4))' in column 'I' as the majority of documents need reviewing every year however I am happy to get rid of this and enter all the review dates manually if it will work better! I then want the formatting to turn it red if the date has passed orange if date is in the next 30 days and green if it's still in date.

I am also happy to change column 'I' to dd-mm-yyyy if this will work better.

Many Thanks,
0
 
Leigh MacpheeAuthor Commented:
Whoops sorry forgot to actually upload file............
SHE-draft.xlsx
0
 
Leigh MacpheeAuthor Commented:
Rob Henson - spot on! Thank you very much!

Thanks again for all your quick answers everyone - much appreciated :)
0
 
Rob HensonFinance AnalystCommented:
Thanks for the feedback.

BTW the NOW funcion gives current date and time whereas TODAY only gives date.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.