Help with Traffic Light System based on upcoming Training Expiry dates

[blobra21]

Hi there,

Just signed up as have come across some of the excellent advice you guys seem to have on similar topics! Have tried to follow guides on similar topics, but unfortunately my lack of understanding is making editing parts of the formula to meet my requirements a bit tricky lol...

Basically I'm trying to make it so that the date entered for a particular training session will be green if it is within the 2 years of that date (so it is still active)

Then Amber if it is 3 months to go until the date expires

Then Red if it has actually passed 2 years and expired.

The closest formula that seemed to do what I want is when using the icon sets on excel:

=DATE(YEAR(TODAY()),MONTH(TODAY())-30,DAY(TODAY())) This one for Green
=DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY())) and This one for Amber

but I really wasn't clear on what I'm meant to change in that formula (was guessing the -30 and -3, but tried various changes and couldn't figure out what I was meant to change it to ) Think those formulas were for 3 years till expiration date, and 6 month warning.

Any help would be much appreciated, so I will know what part of the formula to change for different training as they all have different amount of years till expiration etc.

Thanks team x

ps. sorry if it's just me being very simple minded!

[BadlySpelledBuoy]

Hi and welcome

Could you put together and attach a small sample workbook that demonstrates your data layout. Just a handful of rows of data will be fine, but manually colour the ranges as you'd want them to look if it was all working automatically.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window. Should all make sense from there.

BSB

[davsth]

attach a spreadsheet as suggested but today() is a date so DATE(YEAR(TODAY()),MONTH(TODAY())-30,DAY(TODAY())) is the same as today() - 30 months

today()-yourdate > 730 colour red or the conditional format formula for the cell < today()-730
today()-yourdate > 640 colour amber or the conditional format formula for the cell < today()-640
otherwise the cell is green

[blobra21]

Thanks guys for the responses - have attached an example spreadsheet of what I was trying to do.

The date in the cells is the date that training was completed. Wanted it green if the training is still in date, e.g. in my formula was trying to set it green if within 3 years from the date in the cell.

Then Amber if it is within 3 months from expiration in 3 years time, and red if it has been more than 3 years and is already expired.

[BadlySpelledBuoy]

If you set the conditional formatting to use a formula to determine the result you can use:
Red - Older than 3 years:

Please Login or Register  to view this content.

Amber - Within 3 months of expiration:

Please Login or Register  to view this content.

Green - More than 3 months to expiration:

Please Login or Register  to view this content.

EDATE works by stating how many months forward or backward from a specified date you want the result.
So =EDATE(TODAY(),1) will give you one calendar month added on to today's date whereas =EDATE(TODAY(),-12) will give you the date 12 month's prior to today.

This type of conditional formatting will colour the whole cell rather than just a coloured dot you have in your example.

See attached.

BSB