Share via

Conditional formatting in Excel to highlight Dates Expiring

Anonymous
2021-12-07T16:11:15+00:00

I want to show a table of staff training with the completed dates and the the cells automatically then highlighting in red if overdue, orange due in next three months sand if due in the next year and green if up to date. How do I use conditional formatting to achieve this so I don't have to manually work it out every 3 months please

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144K Reputation points Volunteer Moderator
    2021-12-08T18:08:18+00:00

    1 - The GDPR course was last completed 15/11/2020 and should be renewed annually; so was overdue by 16/11/2021

    As it is overdue I would like it to show in red.

    Caroline,

    I understand, that means there's just one missing step: You have to calculate the expiration date.

    Let us assume you have 15/11/2021 in C2 and the condition in B2.

    =EDATE(C2, IF(B2="Annually",12, IF(B2="Every 2 years",24,36)))+1

    That calculates the expiration date, now use my solution from my first post.

    Andreas.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2021-12-16T07:20:32+00:00

    Hi,

    Feel free to post back if you need any further suggestions.

    Best Regards,
    Mia

    0 comments