Forum Discussion

Copper Contributor

Feb 28, 2024

Solved

Conditional formatting based on difference of two dates

Hi, I guess this should be simple but I have been trying to figure out this for quite a while a cannot get it right.

I want to format a color of a cell (red) based on time passed between the date put in one cell (last time speaking with someone) and today. If the difference is larger than 18 months I want the cell to be colored.

thanks in advance!

excel

Formulas and Functions

SergeiBaklan
Feb 28, 2024
Vojtch575

Conditional formatting rule formula could be like

=EDATE($A1,18) <= TODAY()

5 Replies

SergeiBaklan
Diamond Contributor
Feb 28, 2024
Vojtch575

Conditional formatting rule formula could be like

=EDATE($A1,18) <= TODAY()
- Liz_Schaffer
  Copper Contributor
  Jul 02, 2024
  This is *almost* what I'm needing, but I need it in days, not months. How would I enter that formula?
  - SergeiBaklan
    Diamond Contributor
    Jul 03, 2024
    Liz_Schaffer
    
    Depends on what exactly you'd like to highlight. For example
    
    =TODAY()-$A1 > 20
    
    to highlight dates which are more than 20 days in past.
- Vojtch575
  Copper Contributor
  Feb 28, 2024
  SergeiBaklan Thank you, that worked! You just probably saved me from mental breakdown 😄
  - SergeiBaklan
    Diamond Contributor
    Feb 28, 2024
    Vojtch575 , you are welcome