Expiration based coloring, based on when training was done

I am trying to create a spreadsheet of mandatory annual training certifications that change colors based on when the training was completed. Been experimenting with different formulas, but I just can't seem to get it to work.

Red= >365 days (past due)
Amber=365 to 335 days (30 days out)
Green= <335 days (more than 30 days out)

Basically I recieved the certificates and update the individual's cells with the completion dates.

Example:
John Doe
Cert A (31 OCT 12)
Cert B (30 NOV 12)
Cert C (31 DEC 12)

If the current date was -01 NOV 2013- then Cert A would be RED because it is 1 day past due, Cert B would be Yellow because he still has 29 days left, and Cert C would be Green because he has more than a month left.

Thanks for any help you are able to render.

-Colin Marcum

Does that mean I need establish a due date for each individual training requirement?

Capture.PNG

From the picture we have 30 personnel that each have to do 13 different annual training requirements. And since the due date is based on a year after the course was taken that means a 390 different due dates. I was thinking =TODAY-L3()<365 then it is green; =TODAY-L3()<335 then it is amber; and so on.

I keep getting confused on if a past date is a negative value; thus subtracting it from TODAY's would make it a positive.

Originally Posted by colinmarcum

Does that mean I need establish a due date for each individual training certificate?

If you are setting up to check when they need to be renewed, I would have thought this would be self-evident? If you dont know the due date (or at least have the date last updated, and the certificate duration - 6 mths, 12 mths etc) then how do you know if it expires?

On a side-note to Liz, you can use TODAY() instead of NOW() - NOW() includes the time, which is not needed here. Also, if you will be using this in a LOT of formulas, it will speed things up if you put =TODAY() in it's own cell and then reference that cell in formulas. TODAY() and NOW() are volatile functions, which means they update EVERY time anything is updated in the workbook...pretty much every timer you enter a value/text

you will need 3 different rules anyway, but you will be comparing today's date against when they were last updated.

Also, Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

Duplicate - due to Forum glitch

yes the server has been acting up a bit today