Cell A1 contains the text "FLAG".
Column B contains dates.
I want to apply conditional formatting to cell A1 if any date in column B is less than 2 days from today(), and if the date is past today().
An example of what I’ve tried:
Column B:
10/12/2024
11/12/2024
06/12/2024
Assuming today is 04/12/2024, conditional formatting applied to Cell A1:
=$B$1:$B>TODAY()+2
I would expect cell A1 to flag as it meets the criteria with date 06/12/2024.
=$B$1:$B>TODAY()+1
I wouldn’t expect cell A1 to flag.
If I add the date 01/12/2024 to column B and remove 06/12/2024,
I would expect the cell A1 to flag with either of what I’ve tried.
Neither option has the desired result.
Thanks.
3
Or more traditionally, custom formula:
=countifs(B:B,">="&today()+1,B:B,"<="&today()+2)
or
=countif(B:B,today()+1)+countif(B:B,today()+2)
Custom formula is:
=+sort(xmatch({today()+1;today()+2},B:B))
2
You may use this alternative custom formula:
=REDUCE(FALSE,ARRAYFORMULA((TO_DATE(B1:B)<=(TODAY()+2))*(TO_DATE(B1:B)>=(TODAY()+1))), LAMBDA(a,c, a+c))
Sample Output:
Reference:
- REDUCE function
3