Excel Conditional Formatting

Question

Excel Conditional Formatting

Daniel Guardamondo 45

How do I configure conditional formatting in Excel for years, months, or days?

The header row had names of training and header column has names of people. All other cells have dates that training was completed. I have the formula setup to highlight a cell when annual training is within 30 days of needing renewal.

Format only cells that contain, cell value, less than, =TODAY()-335

When I modify the formula for training due every two years, X number of months, etc…it highlights erratically. For example: training completed on 9/9/2024, due in two years…I want the cell to highlight in 2 months or 60 days. How is this accomplished?

Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff

2024-10-01T06:50:24.8466667+00:00

Hi,

Could you upload a screenshot about the problem so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.
Daniel Guardamondo 45 Reputation points

2024-10-01T14:17:44.93+00:00

I appreciate the follow up. Don't know what happened, but after creating a new spreadsheet and individually copying data something fixed itself. I made this way more difficult than it needed to be. Thank you though!

Accepted answer

0 additional answers

Your answer

Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff

2024-10-01T06:50:24.8466667+00:00

Hi,

Could you upload a screenshot about the problem so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.
Daniel Guardamondo 45 Reputation points

2024-10-01T14:17:44.93+00:00

I appreciate the follow up. Don't know what happened, but after creating a new spreadsheet and individually copying data something fixed itself. I made this way more difficult than it needed to be. Thank you though!

Answer 1

Barry Schwarz 3,746

For a given cell with a date in it, How do you distinguish between training that is required annually and training required biannually? For a given column does everyone require training with the same frequency or do some people need the training annually and other less often?

If everyone requires a particular training with the same other-than-annual frequency, create a conditional formatting rule (for example =A1>TODAY()-670) just for that column, place it in front of the annual rule, and set the Stop If True flag.

Daniel Guardamondo 45 Reputation points

2024-09-30T20:58:09.32+00:00
Each column is for a particular training with its respective renewal interval. $A$2:$A$11 is what I will use for an example. Once I get this figured out, then it could be modify the formula for the rest of the spreadsheet.

I tried implementing your suggested formula below, but it fails to highlight any dates. What simple mistake did I make? Also, is the stop if true flag necessary? I could have different color highlights and a key to differentiate.

Select a rule type = Format only cell that contain

Format only cells with

Cell value

Less than

=$A$2:$A$11 > TODAY() - 670
Daniel Guardamondo 45 Reputation points

2024-10-01T14:15:53.53+00:00

Sigh...I must have had something skewed in the whole spreadsheet. I copied data over individually into a new spreadsheet and the formula you suggested worked. Should have tried that from the beginning because I made it way more difficult than necessary.

Share via

Excel Conditional Formatting

0 additional answers

Your answer