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