SOLVED

# Conditional Formatting for long-term dates

Copper Contributor

# Conditional Formatting for long-term dates

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

8 Replies

# Re: Conditional Formatting for long-term dates

With that

rule formula is

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

# Re: Conditional Formatting for long-term dates

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.

# Re: Conditional Formatting for long-term dates

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

# Re: Conditional Formatting for long-term dates

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
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Conditional Formatting for long-term dates

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.

# Re: Conditional Formatting for long-term dates

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

# Re: Conditional Formatting for long-term dates

It's working perfectly! Days of slogging (then giving up) saved. :)
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Conditional Formatting for long-term dates

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.