Conditional Formatting for long-term dates

[Lois_B]

Hi all

I want to conditionally format a sheet so that when training is coming due it will colour the cell a particular colour.  So, if training should be renewed every 3 years I need the cell to show amber when it's a few months before being out of date.  I have read a similar post for short term dates but don't know how to do it for longer dates.  I've put a selection below.

DSE    (annual)	Security and Data Protection (annual)	Risk management (annual)	Safeguarding (annual)	Manual Handling        (3 yrs)
no records	25/03/2023	25/03/2023	24/01/2022	no records

Any help will be gratefully received!

Lois

[#2]

@Lois_B 

With that

rule formula is

=(A2<>"")*( EDATE(A2, IF( ISNUMBER( SEARCH("annual", A$1) ), 12, 36 ) + $H$1 ) < TODAY() )

[#3]

@SergeiBaklan 

I would not have come up with that in a long time!  Thank you for showing it is possible.

However, how do I adjust the formula so that the cell is:

• Red if today is past due date
• Amber if today is within 90 days of due date
• Green if today is over 90 days of due date

I don't understand how the individual components work to put them together do it.

Could you help again please?

[#4]

@Lois_B 

You need as many rules as different formats you'd like to apply. In this case 3 rules. Due Date we calculate as

EDATE(A2, IF( ISNUMBER( SEARCH("annual", A$1) ), 12, 36 ) )

when 3 rules for 

DD < today (red)

DD >= today+90 (green)

DD >= today

Rules shall be in above order, conditional formatting applies colors for the first rule which returns TRUE.

Finally

[#5]

Hi Sergei
Thanks for another helpful response. Can I push my luck and ask what the 12 and 36 are for in this line?
EDATE(A2, IF( ISNUMBER( SEARCH("annual", A$1) ), 12, 36 ) )
Thanks again
Lois

[#6 · Accepted solution]

@Lois_B 

I assume dates in your sample are the dates when previous training was taken. In header column you mention within the text is that annual training or not. If not when we assume that's once per 3 years training.

Thus we try to find in header cells if that's annual training or not. If annual when formula

IF( ISNUMBER( SEARCH("annual", A$1) ), 12, 36 )

returns 12 (months), otherwise 36.

Finally

EDATE(A2, 12)
or 
EDATE(A2, 36)

returns the date which is 12 or 36 months later than the date in A2. Other words Due Date.

[#7]

Brilliant. Thanks! I shall give it all a go!

[#8]

@Lois_B , glad to help

[#9]

It's working perfectly! Days of slogging (then giving up) saved. 🙂