Using EXCEL to highlight dates past the date in the cell (not today)

Question

Using EXCEL to highlight dates past the date in the cell (not today)

Charlotte Guest 0

Hi, I have a range of data on a spreadsheet used for customer contact. I have one column which has the date in which they were last contacted. I use a specific follow up pattern of sending emails at different intervals. I need to highlight the date in the cell when it gets to different intervals to remind me who to email and which email they need.

So, for instance if I first contacted them on 28.10.26, I would then want to contact them +2, +3, +4, +6, +14 days past the last date entered into the cell.

So, the dates are in column G, but starting from Cell 5 and beyond. It will be frequently updated, so I am happy to do the formula each time I need to change the interval if that makes sense. But as a starting point if I want to highlight Cell G5 purple when 1 day has passed the 28th October, how would I do this?

0 comments

2 answers

Your answer

Answer 1

Apply a conditional-formatting formula that checks whether today is at least one day later than the date in each column G cell.

Highlight after one day

Select the required range, for example G5:G1000.
Go to Home → Conditional Formatting → New Rule.
Select Use a formula to determine which cells to format.
Enter:

=AND($G5<>"",TODAY()>=$G5+1)

Select Format → Fill, choose purple, and select OK twice.
In Home → Conditional Formatting → Manage Rules, verify that Applies to is:

=$G$5:$G$1000

The reference starts with G5 because row 5 is the first row in the selected range. Excel then evaluates G6, G7, and so on automatically. The blank-cell check prevents unused cells from turning purple. This is the same formula-based conditional-formatting method documented for Excel on Windows.^1^

For example, if G5 contains 28 October 2026, the rule becomes true on 29 October 2026 and remains true afterward. Excel supports adding a number directly to a date to advance it by that many days.^2^

Add the follow-up intervals

If each interval needs a different color, create one rule per threshold. For example:

=AND($G5<>"",TODAY()>=$G5+14)
=AND($G5<>"",TODAY()>=$G5+6)
=AND($G5<>"",TODAY()>=$G5+4)
=AND($G5<>"",TODAY()>=$G5+3)
=AND($G5<>"",TODAY()>=$G5+2)

Assign each rule the color corresponding to that follow-up email. In Manage Rules:

Put the +14 rule at the top.
Follow it with +6, +4, +3, and +2, in that order.
Give every rule the same Applies to range.
If available, select Stop If True for each rule.

Rule order matters when several rules are true: Excel evaluates them from top to bottom, and higher rules have precedence.^1^

If the color should appear only on the exact follow-up day, rather than remain afterward, replace >= with =:

=AND($G5<>"",TODAY()=$G5+2)

Updating a date in column G automatically restarts all intervals from that newly entered date.

If no formatting appears, verify that the column contains real Excel dates rather than text. Dates imported or entered in an unrecognized format can be stored as text and therefore will not work correctly in date arithmetic.^2^

References

Answer 2

You can do this using Conditional Formatting in Excel. For your first example, you want cell G5 to turn purple when one day has passed since the date entered in G5.

Assuming G5 contains 28/10/2026, you want the cell highlighted on 29/10/2026. Select cell G5, then go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter this formula:

=TODAY()=G5+1

Click Format, select the Fill tab, choose purple, and click OK.

The cell will now turn purple when the current date is exactly one day after the date entered in G5. For your other follow-up intervals, you can use the same approach, simply changing the number:

Follow-up interval	Formula
+2 days	`=TODAY()=G5+2`
+3 days	`=TODAY()=G5+3`
+4 days	`=TODAY()=G5+4`
+6 days	`=TODAY()=G5+6`
+14 days	`=TODAY()=G5+14`

You can assign a different fill colour to each interval, allowing you to identify which follow-up email is due.

When you are ready to apply this to the entire column, you can select G5 downwards and use the same formula with the appropriate relative cell reference. Excel will automatically adjust the row reference for each cell.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin