CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Aaron Irvine
Aaron Irvine

Reputation: 343

How do I return a cell value if it falls within two date ranges?

I have some locations and dates on the following spreadsheet:

Sheet

Currently the formula in cell E1 returns the location (Col E) for the current event in relation to the start date (Col F). However presently it does not take into account the End Date (Col G).

Screenshot of the current sheet highlighting the problem

As can be seen in the attached image - the End Date has been passed, yet Sweden is still being listed as the active location.

What I would like for the formula to do is return "No Event" if the current date is outside the active dates listed.

IE - From now until 03-06-20 it should return "No Event" and then from 04-06-20 to 26-07-20 it should return "Montreal" et al

Upvotes: 1

Views: 614

Answers (2)

MattKing
MattKing

Reputation: 7773

Your formula seems awfully complicated to me, but i'm not entirely sure I understand what you need.

Here is the formula I would use:

=IFERROR(FILTER(E3:E,F3:F<=C2,G3:G>=C2),"No Event")

Hopefully that helps.

Upvotes: 2

Aaron Irvine
Aaron Irvine

Reputation: 343

I have come up with the following (potential) solution. I don't know whether it is the most efficient way to write this formula, and would appreciate feedback on it:

=ARRAYFORMULA(IF($C$2=MEDIAN($C$2,
TO_DATE(MAX(IF($F3:$F7<C2,$F3:$F7))),
TO_DATE(MAX(IF($F3:$F7<C2,$G3:$G7)))),

VLOOKUP(ArrayFormula(TO_DATE(MAX(IF($F3:$F7<C2,$F3:$F7)))),{$F3:$F7,$E3:$E7},2,FALSE),
"No Event"))

Upvotes: 0

Related Questions