Help with Conditional Formatting

[silentnoise713]

Hi All,



I’m having challenges with getting a conditional formatting formula to work. Hope someone can help here .



The use case is we have staff that that needs training/cert every 3 years. We’d like to have conditional formatting to identify when the person is 3 moths out to their Annv, 2 months out, and 1 month out. I’m looking at using IF(AND) condition but the values in F values, causing problems.



Column Setup:

A1 = Today()

C, D, E = calculated using DATEDIF($B3,$A$1,"Y"/"YM"/"MD")

F = C3/3 – this gets how many iterations of the 3 years notice was sent and the fraction .33 and .66 would indicate 1 or 2 years pass.

G = MONTH($B3)-MONTH($A$1) – this gets the month diff, negative is past while positive is approaching.

https://imgur.com/a/SzYwRpu

I am looking for a condition formal to determine where:

Column F = any value that contains *.667* and column G = 3 to turn Yellow

Column F = any value that contains *.667* and column G = 2 to turn Orange

Column F = any value that contains *.667* and column G = 1 to turn Red



Thank in advance for your help!

 

[silentnoise713]

sry, not sure how the pic link came up 3x and I don't see an edit option. @Mods, could you please remove dup pic links? Thank you.

 

[Peter_SSs]

Welcome to the MrExcel board!

@Mods, could you please remove dup pic links?

Done.
For the future, best method is to 'Report' your post (link at bottom left of the post) & explain in the report what you want done why.

BTW, once you have made a few more posts you will be able to edit (for 10 minutes after posting).