Excel: Setting up Conditional Formatting for Skill Expiry Dates

Question

Hello,

How can I set up conditional formatting for expiration dates in Excel? I have a sheet with names in a column, skills across the top, and dates in the cells specifying when these skills were completed. There are four types of skills that expire after different times, and I want to set up conditional formatting to automatically change the cell color according to these rules:

For skills expiring after six months:

• Turn red on the expiry date.
• Turn amber two months prior to the expiry date.
• Turn green from the completion date in the cell to the time it turns amber.

I need to alter the same formula for the three-year and five-year expiry from the date in the cell:

• Turn red on the expiry date.
• Turn amber three months prior to the expiry date.
• Turn green from the completion date in the cell to the time it turns amber.

I have included a screenshot to explain better.

Answer 1

hi!

1. Mark one cell (say D7 from above example)
2. go to Conditional Formatting,
3. select "New Rule".
4. Select as Rule Type: "Use a formula to determine..."
5. Enter "=DAYS(D7,$A$3)>60"
6. choose the fill color green
7. OK

repeat 1-4

5. Enter "=AND(DAYS(D7,$A$3)>30,DAYS(D7,$A$3)<60)"
6. choose fill color amber
7. ok

repeat 1-4

5. Enter "=DAYS(D7,$A$3)<1"
6. choose fill color red
7. ok

Now when you copy D7 to D8, the conditional formatting is also copied for D8 and so on... just make sure that the range where the rules apply are correct. Th rule itself still shows your original "D7" but reads the value from D8. Mind the "$" for the reference cell!

Hope you got the idea.

Ralf.