How to Change Cell Color Automatically Based on Date

Question

I have some data and based on the amount of time someone gives me to complete the work I want to change the cell color.  I break my tiers into the following:

2 days or less        White/No Fill

3 to 6 days            Green

7 to 13 days          Yellow

14 or more days    Red

So, I would have a Dispatch Date and a current date.  So, if Dispatch Date was 6/27/2022 and current date was 01/6/2022, I would have a cell which would show 14 for the remaining days and I would want this to automatically fill the cell red based on this number. For Dispatch to be 6/28/2022, the cell would be yellow because it has been 8 days. For Dispatch to be 6/24/2022 the cell would be green because it has been 4 days. For Dispatch to be 6/29/2022 & 6/26/2022 the cell would be white/unfilled because it has been 2 days.  What kind of formula/cell conditioning could I use to make this happen?

Answer 1

Hi BuntyKumar2, hope you're doing well. I’m Ian, and I’m happy to help you today.

Please see screenshot for the conditional formatting and formula I created.

This is a user-to-user support forum and I am a fellow user.

I hope this helps, but please let me know if you need anything else.

Answer 2

I have this kind of data.

I want to change the cell color.  as break my tiers into the following:

2 days or less        White/No Fill

3 to 6 days            Green

7 to 13 days          Yellow

14 or more days    Red

Answer 3

Just to clarify you are getting the difference between the start date and dispatch date, is this correct?

Do you need to fill color the whole row?

Answer 4

These articles show how to add multiple conditional formatting rules

.

When doing multiple conditions be very aware of:

• the boundary values in each rule
• the specific condition (less than, vs Less than Equal, etc)
• the order of the rules.

. Conditionally Formatting for Multiple Date Comparisons https://excelribbon.tips.net/T012929_Conditionally_Formatting_for_Multiple_Date_Comparisons.html

by Allen Wyatt         (last updated July 6, 2019)

This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Bev is having a problem setting up a conditional format for some cells. What she wants to do is to format the cells so that if they contain a date before today, they will use a bold red font; if they contain a date after today, they will use a bold green font. Bev cannot get both conditions to work properly.

. Use conditional formatting to highlight information https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f