Conditional Formating of cells based on today's date, and another date

Hello everyone ~ I'm using excel to track provider certifications and need some assistance in formatting a visual cue over a lot of data.

Background:
I use excel to track paramedics to see that they have 4 hours of medical control per year throughout their 3-year certification process. This would be easy to do if everyone were on the same annual cycle, but they are not. Provider certification cycles vary by month throughout the three years based on when they initially certified. I have about 100 paramedics to follow, each on their own different recertification cycle. It is difficult to see what year an individual is in, whether they have met their required hours, or how many hours they have left to do.

I use a single summary sheet to track providers, and each has their own separate sheet that feeds the summary sheet. I would love to see the current year a paramedic is in as a highlighted cell, based on (today’s date). That way I could see what year they are in, and if they met the necessary hours for this year.

Attached is an example, where I have used conditional formatting to cue how soon until a provider must recertify, and text color formatting based on the value in the cell. However, I cannot figure out how to highlight a cell in the MD column based on what year of the cycle they are actually in.

For example, I would like to highlight E3 to show that Smith is in his third year, D4 to show that Adams is in his second year, C7 to show that Cook is in his first year, (based on today’s date) etc... I hope I am explaining this well enough!

Thank you much!

David
CME File Example.xlsx

Hi David, have you tried using data bars? Conditional Formatting > Data Bars > More Rules > Format cells that contain... Then just format cells that have values greater than zero?

I do like the look visually of the data bars, however I would like to highlight the cell of what year the person is in at this moment (their 1st, 2nd or 3rd) so that I could glance at a large group and see what they need. At the moment I have to figure out each row based on the expiration date and today's date to figure out that it's ok to have zeros in a column instead of other numbers.

I have tried conditional formating with the following formulas, but it doesn't seem to work past the first or second column. I placed each rule in order and then to stop if true:
=(C16-TODAY())<=365 to highlight cell blue showing the third year
=(C16-TODAY())<=730 to highlight cell green showing the second year
=(C16-TODAY())<=1095 to highlight cell red showing the first year

This way, I could look at the blue colored cells to see who has accomlished what hours for this year's period, but based on their expiration date.

I apologize... referring to the previous email and the previously attached example, the cell source would not be C16, but the corresponding B2 through B9 depending on the row. I had copied the formula from the original file.