Excel Conditional Formatting date within x days

0
votes

I have one date in C4. And I have dates calculated in Column I.

I am trying to highlight with conditional formatting any date in column I that is before the date in C4 and within the next 45 days of today.
Criteria 1: Date in Column I is sooner than date in cell C4

Criteria 2: 45 days or less from today = True

This is the formula I have so far, but can't seem to get working correctly - 

=AND(C4>TODAY(), C4-TODAY()<=45)

I can get the formula to work on a single cell as follows:

=AND($C$4>$I$13, $C$4-TODAY()<=45)

I tried to copy down, but that just gets it to apply to the entire the formatting to the entire column

1
excel-2010conditional-formatting

1 Answers

0
votes

Solution:

=AND(I4<$C$4,ABS(I4-TODAY())<=45)

Was able to apply this range to the whole column and where you see I4, the formula would update to the relative cell. Interesting Note, I had been trying this in 2010 but in reverse (subtracting c4 from today) and was having trouble with the formula not saving without automatically adding absolute cell references.