Excel Formula - formula based on either today's date OR entered date to turn cell color

[Betty Swanson]

I have Excel 2010

The column AA will be based on two things…the date in AB and/or the current date.

The objective is to turn the cell in AA yellow, orange or red based on the proximity of the dates to either the current date or the submitted date in column AB.

Yellow – within 5 business days

Orange – within 2 business days

Red – late

If there is not an Actual Submission date in column AB I want it to use the current date when the file is opened to display the highlights. If there is a date in AB I want to use the actual date in AB compared to the date in AA.

Does that make sense? Is that possible?

[#2]

Hi Betty,

You may use conditional formatting https://support.office.com/en-us/article/use-formulas-with-conditional-formatting-fed60dfa-1d3f-4e13... applying 3 rules,

for red like

=IF(ISBLANK($AB1),TODAY(),$AB1)-$AA1<0

and <=2 for orange; <=5 for yellow. Put red rule on the top, next orange, next yellow.