Conditional formatting

[geordieisdownunder]

I am trying to set formatting for when a certificate is due to expire.
I have put a today formulae in one cell for current day.
In another cell I have an expiry date (there is multiple cells in the column each with different expiry dates)
I want either of these cells to highlight different colours for expired(red), 6 months to expiry(amber) and 6 months to expiry(green) and more than 6 months clear, no formatting just dates
Thanks in advance
Colin

 

[Special-K99]

highlight different colours for
expired(red), OK
6 months to expiry(amber) OK
and 6 months to expiry(green) That's the same as the one above, please clarify exactly what you want
and more than 6 months clear, OK

 

[geordieisdownunder]

Yes sorry should be:
More than 6 months clear no formatting
6 months - green
3 months - amber
Expired - red
Thanks

 

[Special-K99]

You havent mentioned any cell references, so I will have to assume some things. This means you will have to adjust any solution given (specifically cell references) to match your actual spreadsheet.

A1=TODAY()
B1:B100 a list of expriy dates

Select B1:B100

Conditional Formatting
New Rule
Use a formula to determine...

=ISERR(DATEDIF(A$1,B1,"m"))
format as red

=DATEDIF(A$1,B1,"m")<6
format as green

=DATEDIF(A$1,B1,"m")<3
format as amber

 

[geordieisdownunder]

Hi, thank you for your help. I'm pretty poor using excel, trying to learn. The formulas you gave did not seem to work. Everything was highlighted in amber for some reason. I think I maybe need to reference the year of expiry too. the expiry I am looking at using in this sheet is 5 years.

I kinda mocked up a spreadsheet as below (don't think I can attach a sample) there would be about names on the list, probably increasing.
So when:
TODAY (D) is 6 months from expiry (E) format green
TODAY (D) is 3months from expiry (E) format amber
TODAY (D) is 6 beyond expiry (E) format red

More than 6 months no formatting

37 A B C D E F 38 NAME OUTLET ROLE TODAY'S DATE CERTIFICATION EXPIRES CERT. NUMBER 39 colin1 venue1 chef1 30-08-17 01-05-18 123456 40 joe2 venue2 waiter2 30-08-17 01-05-18 678910 41 jane3 venue3 chef3 30-08-17 30-08-20 111213 42 percy4 venue4 chef4 30-08-17 14-08-22 234532 <tbody> </tbody> 6 months to expiration - green formatting 3 Months to expiration - amber formatting past expiration date red formatting <colgroup><col span="4"></colgroup><tbody> </tbody> more than 6 months no formatting Thank you again

<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>

You havent mentioned any cell references, so I will have to assume some things. This means you will have to adjust any solution given (specifically cell references) to match your actual spreadsheet.

A1=TODAY()
B1:B100 a list of expriy dates

Select B1:B100

Conditional Formatting
New Rule
Use a formula to determine...

=ISERR(DATEDIF(A$1,B1,"m"))
format as red

=DATEDIF(A$1,B1,"m")<6
format as green

=DATEDIF(A$1,B1,"m")<3
format as amber