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

[TABLE="width: 908"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]37[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]NAME[/TD]
[TD]OUTLET[/TD]
[TD]ROLE[/TD]
[TD]TODAY'S DATE[/TD]
[TD]CERTIFICATION
EXPIRES[/TD]
[TD]CERT. NUMBER[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]colin1[/TD]
[TD]venue1[/TD]
[TD]chef1[/TD]
[TD]30-08-17[/TD]
[TD]01-05-18[/TD]
[TD]123456[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]joe2[/TD]
[TD]venue2[/TD]
[TD]waiter2[/TD]
[TD]30-08-17[/TD]
[TD]01-05-18[/TD]
[TD]678910[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]jane3[/TD]
[TD]venue3[/TD]
[TD]chef3[/TD]
[TD]30-08-17[/TD]
[TD]30-08-20[/TD]
[TD]111213[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]percy4[/TD]
[TD]venue4[/TD]
[TD]chef4[/TD]
[TD]30-08-17[/TD]
[TD]14-08-22[/TD]
[TD]234532[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 348"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD="colspan: 4"]6 months to expiration - green formatting[/TD]
[/TR]
[TR]
[TD="colspan: 4"]3 Months to expiration - amber formatting[/TD]
[/TR]
[TR]
[TD="colspan: 3"]past expiration date red formatting[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
more than 6 months no formatting

Thank you again

[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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