Nested Date Range Test

[Seahorse]

Columns AA, AD and AG are populated with dates. I need to confirm if any of those contain a date that is plus or minus 7 days from Today() however my first stab results in my test dates (2 +/- 5 and 2 +/- 14) all being FALSE. Clearly I need to get it working before I start nesting, but not sure what's wrong in the first place...

=OR($AA5=TODAY()+7,$AA5=TODAY()-7)

 

[Jake Collins]

Dear Seahorse

Your formula will only check if the date is either 7 days ago or 7 days in the future, i.e. for today (17th march) will only return true for 10th march or 24th march. If you want a formula to return true for all dates between 10th march and 24th march you'll need something like:

=AND(AA5>=TODAY()-7,AA5<=TODAY()+7)

 

[Somendra Misra]

Hi Seahorse,

Try this,

=AND(AA1>=TODAY()-7,AA1<=TODAY()+7)

Regards,

 

[Seahorse]

Thanks, I realised it was exactly +/- 7 (but not 6, 5, 4...) a little while ago. This doesn't seem to extend to the next column (works fine for one though) and swapping to an OR also seems to fail.

=AND(AA5>=TODAY()-7,AA5<=TODAY()+7,(AD5>=TODAY()-7,AD5<=TODAY()+7))

or

=AND(AA5>=TODAY()-7,AA5<=TODAY()+7,OR(AD5>=TODAY()-7,AD5<=TODAY()+7))

If I go for a full OR a blank cel in AD gives a true even if AA is say - 2 weeks.

=OR(AA5>=TODAY()-7,AA5<=TODAY()+7,AD5>=TODAY()-7,AD5<=TODAY()+7)

 

[Seahorse]

So I have generated a smally test spreadsheet with comments showing what I am trying to acheive and what goes wrong. OR should be able to check the three coloums for any TRUE instance of the date being +/1 7 days from TODAY() and on the surface appears to work. however, blank coloums also return TRUE when they should be FALSE. Furthermore, deliberately changing the dates so that all 3 are FALSE still gives TRUE.

Please see attached which demonstrates the problem.

 

[NARAYANK991]

Hi ,

What you should be using is :

=OR(AND(B2>=TODAY()-7,B2<=TODAY()+7),AND(C2>=TODAY()-7,C2<=TODAY()+7),AND(D2>=TODAY()-7,D2<=TODAY()+7))

Enter this in A2 , and copy down.

Narayan

 

[Somendra Misra]

Hi,
Try below array formula, enter with Ctrl+Shift+Enter, and not just Enter.

=IF(MMULT((B2:D2>=TODAY()-7)*(B2:D2<=TODAY()+7),{1;1;1})>=1,TRUE,FALSE)

Regards,

 

[Debraj]

I am not sure.. but still a blind shot..
Check with below Array Formula..
It will give you TRUE, only if any of the date is fall Perfect +/- 7 from today.
Please confirm the formula by pressing Ctrl + Shift + Enter, not just Enter

=OR(ABS(TODAY()-$B2:$D2)<=7)

 

[Haseeb A]

Hello SeaHorse,

Try, both with just ENTER

=(COUNTIFS(B2:D2,">"&TODAY()-7,B2:D2,"<"&TODAY()+7)>0)

Or,

=(COUNT(MATCH(TODAY()-{0,1,2,3,4,5,6}*{1;-1},B2:D2,0))>0)

TODAY() is a volatile function. Better is, enter in a cell =TODAY(), then reference that cell in the formula.