Excel - How do I get cells to change colour when they are nearing future expiry dates

Question

Hi,

I have a spreadsheet with staff names down column A, and across the top I have different training courses they attend which they are required to refresh after 1, 2 or 4 years (depending on training). I want to be able to enter the date they attend, then when they have 60 days until expiry for it to change to orange, and 30 days out change to red.

Please help!

Answer 1

Hi,

As mentioned above, it can be done with the use of Conditional Formatting.

Here's how you can do this-

1. Select the range of cells containing the dates for each staff member and training course. For example, if the dates are in cells B2:F14, select that range.
2. Go to the "Home" tab in the Excel ribbon and click on "Conditional Formatting" in the "Styles" group. Then, choose "New Rule" from the dropdown menu.
3. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
4. Enter the following formulas for each condition:

For 60 days until expiry (orange):

• Formula: =AND(B2<>"" , B2-TODAY()<=60 , B2-TODAY()>=0)
• Format: Choose the orange color or any other formatting you prefer.

For 30 days until expiry (red):

• Formula: =AND(B2<>"" , B2-TODAY()<=30 , B2-TODAY()>=0)
• Format: Choose the red color or any other formatting you prefer.

Make sure to adjust the cell references (B2) to match the actual cell where the date is entered.

5.) Click "OK" to apply the formatting rule.

Now, when you enter a date in the cells, they will automatically change color based on the conditions you set.

Best Regards.

Answer 2

1.Select Range

2. home>styles >conditional formattin >New Rule...

3.Select a Rule Type (Any Optional Setting)

4.Click on 'Format' Button (To Formart Setting Color/Style/And...)

5.OK