So, at work we have dozens of CBT courses that we are required to take. Some are annual requirements but most are "one and done" requirements. I'm making a spreadsheet and want to put the letter X in the column for each person that the training is required (to make things more complicated, some training isn't required for everyone, so I want to put N/A for those or simply leave them blank). I want these letter X's to show up as red. I can do that with a simple conditional format.

Now, when someone completes a course, I want to put the date in the cell and make the sheet change it to green. NOTE: THERE IS NO EXPIRATION DATE FOR THIS. Everything I google tells me how to change this for various expiration dates. I want the sheet not to care what date is put in. I can put in July 4, 1776 and it should be the same green cell as today.

For the annual ones, I would like the same thing but I want the X to be red and once a date is in it's green until a year from that date, in which case, it too turns red again.

Any help would be appreciated.

Edited to add screenshot: screen pic

pic of cell format

New contributor
Banjoclan is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • To clarify, you want to turn red when a cell contains "X" and turn green when that same cell contains a date? (As opposed to putting the date somewhere else and the "X" turns green based on that other cell.) – Engineer Toast Jul 21 at 15:40
  • Correct. Although, ideally I would have a master sheet with all the required crew positions and corresponding training and then a current crew list which would populate that somehow, but I think that's beyond what I can do. My plan is to do what you describe and then just copy and paste that tab on a monthly basis. This is what I've done on other jobs I've been on. – Banjoclan Jul 21 at 15:46

Please check if the following formula is helpful to you.


enter image description here


You can add a second conditional format. I used a formula =ISNUMBER(A1). The A1 reference should be the top-left cell in the "Applies to" range. Note that ISNUMBER returns true for any number, not just dates. Given your use case, though, you expect cells to be one of: X, N/A, blank, or a date. These two conditional formats in the screenshot below will work for that use case.

enter image description here

Updated per OP's update:

This part of the Conditional Formatting Rules Manager screenshot indicates that there is a problem with your formula:


This can happen if the cell you're originally referencing was deleted. (It probably happens due to other reasons, too, but that's the one I encounter most often.) You need to edit that rule and fix the formula. Since your "Applies to" range is =$C$2:$P$32, you need to edit the formula to refer to the top-left cell in that range: C2. Note that you should make this a relative range (C2) and not an absolute range ($C$2). If you use an absolute range, it will evaluate every cell in the "Applies to" range based on the content in C2 instead of the content in that particular cell.

  • OK, so I got the red highlight to work but the date part isn't turning anything green. – Banjoclan Jul 21 at 16:45
  • @Banjoclan Could you update your question with a screenshot of how far you've gotten? – Engineer Toast Jul 21 at 17:19
  • I added a screenshot to the original question. I don't think I can add it directly to the answers. – Banjoclan Jul 21 at 19:02

Your Answer

Banjoclan is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.