Excel Conditional formatting looking for date - 30 days change cell color

Hi Experts,
I need a conditional format that will change a cell to yellow if the date is 30 or 60 or 90 days before today's date.,

Example, I have a column of expiry dates, (actually multiple columns but I will just copy to others later), I need to change cell color to when date is about to expire.

If cell's date is expiring 30 days from today or already expired then turn cell yellow.
That's it.
Thanks

If I could use a cell to dictate the 30 - 60 - 90 in the formula that would be awesome.
Thanks
Chris
chris pikeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
You can use a formula instead of a value comparison (as in above examples) to check for blank cells and ignore conditional formatting.

I've expanded on your question by creating an example workbook that applies three levels of conditional formatting:
Red: if Expiry Date is 90+ days old
Orange: if Expiry Date is 60+ days old
Yellow: if Expiry Date is less than or equal to 30 days from today's date

The rule are applied in the order stated above; if the first condition is true, the conditional formatting stops there, otherwise, it continues down the list.
EE-CondFormat.png
The rules are:
Red:  =IF(AND(A2<>"",A2<=TODAY()-90),TRUE,FALSE)
Orange: =IF(AND(A2<>"",A2<=TODAY()-60),TRUE,FALSE)
Yellow: =IF(AND(A2<>"",A2<=TODAY()+30),TRUE,FALSE)

Note that the formulas use relative referencing (A2, not $A$2) so that you can just expand the range to which is applies and still have the formatting work.

My example also has an aging column to the right as a check to ensure that the proper formatting applies.

Regards,
-Glenn
EE-CondFormat.xlsx
0
 
Helen FeddemaCommented:
Can you be more precise?  When you say "the date is 30 or 60 or 90 days before today's date", do you mean exactly 30, or 60, or 90 days, or (say) 30 - 59, 60 - 89, or 90+ days?  Do you want different colors for each date or date range?  Later  you say just yellow for expiring 30 days from today.
0
 
Roy CoxConnect With a Mentor Group Finance ManagerCommented:
Hi Chris

Select the cell and select Conditional Formatting in the Ribbon

Screenshot-2016-09-16-17.00.52.png
Then Manage Rules and New Rule

Screenshot-2016-09-16-17.03.35.png
When the New Formatting Rule window appears, select Format only cells that contain as the rule type.
You can apply multiple formulas as required and formats.
Screenshot-2016-09-16-17.06.04.png
Enter the formula and choose the Format

The next step is to enter the range for the format to apply to

Screenshot-2016-09-16-17.09.50.png
Remember that using a Table for the data then the range becomes dynamic and the Conditional Formatting expand as data is added or contract as data is removed.

Let me know if anything is not clear.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
chris pikeAuthor Commented:
Hi Roy, Again.... :)
I changed it a little bit, I did Less than and then +30 days, it highlights anything almost expired or already expired.

How do i ignore blank cells, as above instructions highlights the blank cells, or do I need a second conditional format?
Thanks
0
 
Roy CoxGroup Finance ManagerCommented:
My Method still works, just add the other rules

Screenshot-2016-09-17-10.00.04.png
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
@Roy - using your method doesn't allow for blanks

@Glenn - don't need the IF statement, the Conditional Formatting is checking for a TRUE statement to apply the format, formula can be just:

=AND(A2<>"",A2>TODAY()+30)

Thanks
Rob
0
 
chris pikeAuthor Commented:
Thanks Everyone.
Got it done.
Good work.
0
 
Roy CoxGroup Finance ManagerCommented:
Hi Chris

Pleased to help
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.