May 05 2023 11:15 AM
Hello, I need help with creating a highlighting condition based on dates. I would want colum M (previously positive) to highlight IF the date in cell T (Test 1 date) is within 60 days of each other.
May 05 2023 11:24 AM - edited May 05 2023 11:32 AM
Select cells in column M, from M2 down. M2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND(T2<>"', T2<M2+60)
=AND(T2<>"", T2<M2+60)
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
May 05 2023 11:29 AM
May 05 2023 11:31 AM
Do you use comma as decimal separator? If so, use semicolon instead of comma in the formula:
=AND(T2<>""; T2<M2+60)
May 05 2023 11:32 AM
May 05 2023 12:05 PM
May 05 2023 12:11 PM - edited May 05 2023 12:12 PM
May 05 2023 12:34 PM
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
May 05 2023 12:57 PM
@Hans Vogelaar yes I have attached the blank document
May 05 2023 01:09 PM - edited May 05 2023 03:05 PM
Thanks. The problem is you applied the rule to the entire column $M:$M instead of what I suggested, but the formula still referred to M2 and T2. If the rule applies to the entire column, the formula should refer to M1 and T1.
=AND(T1<>"", T1<M1+60)
See the attached version. (Edited to attach the correct version)