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

2 answers

Your answer

Answer 1

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  

Answer 2

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 answers

Your answer