CODEWITHSUNDEEP
arraysdategoogle-sheetsgoogle-sheets-formulaarray-formulas
SilentAssassinShadow09
SilentAssassinShadow09

Reputation: 13

Multi Condition Array With DateValue

I'm looking to create an Array Formula in Google Sheets that checks if two dates are within certain time periods.

Currently, I am using:

=ARRAYFORMULA(IFERROR(IF(DATEVALUE($B3:$B)-DATEVALUE($A3:$A)<E2,TRUE,FALSE),"")

Which is giving me the following results:

https://i.sstatic.net/SxDeP.jpg

The issue is that if a value is True within 90 days, it remains true within 7300 days. I would like each column to be exclusive to its current period. I had hoped the following would work, however it just makes everything to True.

=ARRAYFORMULA(IFERROR(IF(DATEVALUE($B3:$B)-DATEVALUE($A3:$A)<90 & DATEVALUE($B3:$B)-DATEVALUE($A3:$A)>1095,TRUE,FALSE),""))

Anyone know if there is a way for this to work? I appreciate it a ton.

Upvotes: 1

Views: 147

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(A3:A="",,{DAYS(B3:B, A3:A)<C2, 
   (DAYS(B3:B, A3:A)>=C2)*(DAYS(B3:B, A3:A)<D2)=1,
   (DAYS(B3:B, A3:A)>=D2)*(DAYS(B3:B, A3:A)<E2)=1,
   (DAYS(B3:B, A3:A)>=E2)*(DAYS(B3:B, A3:A)<F2)=1}))

0

Upvotes: 1

Related Questions