Forum Discussion
Excel Formula - formula based on either today's date OR entered date to turn cell color
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?
Hi Betty,
You may use conditional formatting https://support.office.com/en-us/article/use-formulas-with-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f 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.
