Forum Discussion

Lprez's avatar
Lprez
Occasional Reader
Apr 30, 2026

Hightlight date cells based on expiry

Hi,  I have inherited a spreadsheet of employees who have been on various courses based on their roles, the courses either have a 12 month, 2 year or 3 year expiry date, they are ordered in their relevant columns.  the dates are past dates and i need to add a formula so i can see when each person is 60 days out from needing to renew their course (amber) is over the due date (red) and the rest can be green, this gives me enough time to book the ambers in with pleny of notice and shows the current expired courses to prioritise.  i think i can use the TODAY() formula but as each date field is a historic date  i need to work off each individual field to give that persons due dates as they have gone on courses at different dates from each other.

currently it is a nightmare tyring to keep track and i need a simpler visual que.

 

any help would be great.

 

thank you.

excel
Formulas and Functions

1 Reply

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Apr 30, 2026

    so the formula you want is something like:

    =EDATE(C2,D2)

    this will give you the number of months in D2 after the date in C2.  This will give you the Expiration date of the course based on the date completed (C2) and the expiration duration (D2).

    As for easy to see you can then either do a simple conditional formatting on that Expiration Date column like using a Red/Yellow/Green Icon Set.

    or you could highlight the whole row (or any part thereof) usung a custom formula like this:

    here is the custom formula entry:

    notice how I highlighted the cell A2:D2 and the formula is =EDATE($C2,$D2)<TODAY()+60

    the $ are important because you want excel to ALWAYS use columns C and D and NO $ before the 2 because you want excel to look at the same column as the cells it is checking.  The +60 is for 60 days after TODAY().

    I also did the same formula without the +60 for the Red formatting and made sure the red rule is above the yellow rule so it will take precedence.