Home

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

Highlighted
Betty Swanson
Occasional Visitor

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?

1 Reply

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies