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

Charlotte Guest 0 Reputation points
2026-10-30T10:27:12.9833333+00:00

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?

Microsoft 365 and Office | Excel | For business | Windows
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Marcin Policht 112.2K Reputation points MVP Volunteer Moderator
    2026-10-30T11:16:03.4533333+00:00

    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

    Was this answer helpful?

    0 comments

  2. AI answer

    2026-10-30T10:27:55.67+00:00

    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

    1. Select the required range, for example G5:G1000.
    2. Go to Home → Conditional Formatting → New Rule.
    3. Select Use a formula to determine which cells to format.
    4. Enter:
    =AND($G5<>"",TODAY()>=$G5+1)
    1. Select Format → Fill, choose purple, and select OK twice.
    2. 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:

    1. Put the +14 rule at the top.
    2. Follow it with +6, +4, +3, and +2, in that order.
    3. Give every rule the same Applies to range.
    4. 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

    1. Windows
    2. DATE function | Microsoft Support
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.