Conditional formatting after a time period relative to the date in a cell

[daithiboy]

Hello All,

I have a sheet with a list of names in column A and the dates that they attended training in column B (and subsequent for different training types).

As, each person has to redo their training after two years, I was hoping that I could get the cells in column B to go red once two years has passed based on the various dates in column B.

[TABLE="class: grid, width: 148"]
<tbody>[TR]
[TD]Name[/TD]
[TD="align: center"]Training[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]12/02/2015[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD="align: right"]05/12/2013[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]08/06/2012[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD="align: right"]09/03/2016[/TD]
[/TR]
</tbody>[/TABLE]

Any suggestions? I really don't know how to proceed.

VBA or some quirk of general Conditional Formatting is fine with me!

Many thanks in advance,
Dave

 

[wideboydixon]

Assuming the names are in column A and the dates in column B, select column B and then Conditional Formatting > New Rule > Use a formula to determine which cells to format

Enter this as the formula:

=AND(ISNUMBER(B1),DATEDIF(B1,TODAY(),"Y")>=2)

Set the appropriate format (e.g. fill red) and it should identify Dave and Mike as overdue dates.

WBD

 

[daithiboy]

Many thanks Wideboydixon, that worked great. Do you mind if I ask if it is possible to expand it so the date ranges <1.5(years) are formatted Green, 1.5 - 2 Orange and >=2 is red?

I've tried using three different rules for the time frames but the formula will have the orange rule (>=1.5) overruling the red one (>=2). I've tried moving them up and down the rule order but it seems that whichever is higher up the order wins out.

I hope that is clear. The first solution works great and this would just be some window dressing that I am curious about.

Many thanks again,
Dave