Conditional formatting icon sets for expiry 12months from date in cell

[adamtaylad]

I am producing a skills matrix, I would like to be able to input a date into any cell in the given range for the conditional formatting and it will apply a 12month expiry, it will have the Green tick for the first 11 months, amber in the last remaining month and then once surpassed 12months it would have the Red Cross.

I have tried versions of =TODAY() formulas but I have only got them to work where it will remain Green until it has surpassed 12months and it will not make use of the amber.

I don't need it linked to today, nor do I need it linked to a certain cell - I simply need it to understand the date value in the given cell and work out when it would be due.

I have used a similar formula in the past but I can't for the life of me remember it

Need help from the excel wizards, please and thank you!

 

[PeteWright]

Just one question: What should the input date be compared to?

 

[adamtaylad]

Just one question: What should the input date be compared to?

Dumb it down for me

I enter a particular date into the cell when something was achieved, this will be valid for 12 months and display green icon, in the last remaining month it would change to amber and then would change to red when it has expired.

 

[PeteWright]

This should be what you're looking for:

Try following setup:

Explanation:

1 calendar year = 365.2425 days
1 calendar month = 30.436875 days
hence
365.2425 days = 12 months
334.805625 days = 11 months
thus

= TODAY() - 365.2425

calculates the date 12 months before today

= TODAY() - 334.805625

calculates the date 11 months before today

 

[adamtaylad]

This should be what you're looking for:

View attachment 117081


Try following setup:

View attachment 117075

Explanation:

1 calendar year = 365.2425 days
1 calendar month = 30.436875 days
hence
365.2425 days = 12 months
334.805625 days = 11 months
thus

= TODAY() - 365.2425

calculates the date 12 months before today

= TODAY() - 334.805625

calculates the date 11 months before today

So far this looks like a winner! could this same rule be applied if I wanted to apply it over a 3 year period? 1095.72.. instead of 365?

 

[PeteWright]

Can for sure be done. Let me know the details.

 

[MARK858]

Personally I would use EDATE

=EDATE(TODAY(),-12)

For 12 months

=EDATE(TODAY(),-36)

For 36 months (3 years)