Forum Discussion

Ashlea Schwarz's avatar
Ashlea Schwarz
Copper Contributor
Feb 27, 2018

Excel Highlight date within 60 days of another date

I have 4 columns. Column C is the Paid Date. Columns J, K, and L contain dates that I want to compare to Column C date. I want to highlight any date in column J, K, or L that is within 60 days of the date in Column C (I would ideally like to look back 60 days only--so 60 days before Column C date). I have thousands of rows to apply it to. 

Thanks!

Formulas & Functions
Requests
  • Willy Lau's avatar
    Willy Lau
    Steel Contributor
    Feb 28, 2018

    Assume 

    1. Data starts from row 2 to row 1000

     

    Steps

    1. Select J2
    2. Create a name in Name Manager, NoOfLookBackDays 
      =60
    3. Create a name in Name Manager, PaidDateInclusive 
      =TRUE
    4. Create a name in Name Manager, CFCheckingValue 
      =(INT(Sheet1!$C2) - INT(Sheet1!J2) + PaidDateInclusive) <= NoOfLookBackDays
    5. Home > Styles > Conditional Formatting > Manage Rules > New Rule... > Use a formula to determine which cells to format
    6. Type the following formula for the rule 
      =CFCheckingValue
    7. Apply a format to fill a background colour
    8. Click OK buttons to confirm and back to Conditional Formatting Rules Manager
    9. Type the following reference address to box of Applies To for the rule 
      =Sheet1!$J$2:$L$1000

     

    Try it to see if it is what you want.

     

    If other community members have a better solution, please share.  I feel myself limit in the approach which I get fimilar with.

     

Resources