Forum Discussion

Lois_B's avatar
Lois_B
Copper Contributor
Aug 24, 2023

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 records25/03/202325/03/202324/01/2022no records

Any help will be gratefully received!

Lois

  • 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.

    • Lois_B's avatar
      Lois_B
      Copper Contributor

      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?

      • 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

Resources