Conditional Format By Date

Question

Conditional Format By Date

Ben Foubister 40

Hi.

i am trying to conditional format column D of my spreadsheet based on how many days have passed from the date in the cell. E.G. Cell D3 has 18/3/26 in it so i would want that to be filtered orange but cells with more recent dates would be green.

0 comments

Answer accepted by question author

2 additional answers

Your answer

Answer 1

Barry Schwarz 5,356

The following conditional formatting formulas can be used to achieve your stated results. The formulas must be in the order shown and each must have the "Stop if True" box checked.

TODAY()-151>=INT(A1)                                dark red
TODAY()-120>=INT(A1)                                bright red
TODAY()-90>=INT(A1)                                 orange
TODAY()-61>=INT(A1)                                 bright green
TODAY()-46>=INT(A1)                                 pale green
TODAY()>=INT(A1)                                    white

Replace all references to A1 with the first data cell in your range.

Your specifications were ambiguous for 90 and 120. I removed the ambiguity by including those values in the range where they first appeared.

Ben Foubister 40 Reputation points

2026-04-09T07:42:05.6+00:00

Hi Barry,

I tried this but nothing seems to be happening. Unsure if i have done something wrong? going by this until I add the other rules everything newer than 90 days should be orange?
Barry Schwarz 5,356 Reputation points

2026-04-09T13:00:34.2233333+00:00

The third rule is not an automatic "everything else". It affects only dates at least 90 days old. The sample you show maxes out at 66 days.

Change one of your dates to January 1 temporarily to see orange. Or simply implement the remaining three formulas to see all the colors you expect.
Ben Foubister 40 Reputation points

2026-04-09T13:15:13.51+00:00

Hi Barry, I got it working eventually. Thank you for the help. :)

Answer 2

Henry-N 11,820 Microsoft External Staff Moderator

Hi @Ben Foubister,

If you want to change the color of a cell based on the date value it contains, you can try using Color Scales.

For example, as shown in the image below, more recent dates can be displayed in red. You can also reverse the color scale if you prefer newer dates to appear in green instead.

User's image Thank you again for your time and understanding. While my initial response may not fully resolve your issue, I’d be happy to learn more about your specific scenario so I can assist you more effectively.

I appreciate your patience and I’m here to help. I look forward to your response.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread  

Henry-N 11,820 Reputation points Microsoft External Staff Moderator

2026-04-08T14:37:18.96+00:00

Hi @Ben Foubister,

Just want to check if you have any new update on the issue feel free to share here. Thank you so much for your cooperation we truly appreciate your time and effort. Looking forward to hearing from you soon.
Ben Foubister 40 Reputation points

2026-04-08T14:43:28.8133333+00:00

Hi Henry,

this may work though its not something i am familiar with. essentially the dates need to be broken up into the below so any advice on this would be appreciated. i also added some ref data from my current sheet

Answer 3

Use conditional formatting with date-based formulas that compare each cell in column D to today’s date.

Example: format older dates orange and recent dates green.

Select the range in column D (for example, D3:D100).
On the Home tab, in the Styles group, select Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.

Orange for “older than X days ago” (for example, more than 30 days before today): 4. In Format values where this formula is true, enter:

=D3<TODAY()-30

Select Format…, choose an orange fill, then OK.

Green for “within the last X days” (including future dates): 6. Add another New Rule with formula:

=D3>=TODAY()-30

Set the format fill to green, then OK.
In Conditional Formatting > Manage Rules, ensure the orange rule is listed above the green rule if needed, and confirm the Applies to range covers all required cells.

Adjust 30 to the number of days that should separate orange from green. Excel will evaluate each date in column D against TODAY() and color accordingly.

References:

Use conditional formatting to highlight information in Excel

Share via

Conditional Format By Date

2 additional answers

Your answer