Return TRUE if date range is within date range

Ask for Help
1

In Sheet 1, I have column A with an event’s Start date and column B with the event’s End date.

In Sheet 2, column A has a formula that gives me the coming Saturday’s date:
=TODAY()-WEEKDAY(TODAY(),3)+IF(WEEKDAY(TODAY(),3)>5,11,5)
Column B gives me the coming Sunday’s date:
=TODAY()-WEEKDAY(TODAY(),3)+IF(WEEKDAY(TODAY(),3)>5,11,6)

I need to have a cell TRUE in Sheet 1 if the event is happening during the coming weekend.

I have no idea how to do this…

Thanks!!

3

Edit: See later answer that actually works as requested:

@filipa I think this will work. Put the formula below in cell C1 of your Sheet1 example. No need for Sheet2. I didn’t do a ton of testing but I’m pretty sure it will work for you.

={"ThisWeekEnd";ARRAYFORMULA(IF(ISBLANK(A2:A),,IF(A2:A+1 < (TODAY()-WEEKDAY(TODAY(),3)+IF(WEEKDAY(TODAY(),3)>5,11,5)),IF(B2:B>(TODAY()-WEEKDAY(TODAY(),3)+IF(WEEKDAY(TODAY(),3)>5,11,5))-1,TRUE,FALSE))))}
4

Thank you, George! It works if the event happens in both saturday and sunday. But it can happen that the event ends on a Saturday or that it only lasts one day (so the start and end date is the same), and in those cases the formula’s result is false. How could I change it so it considers those cases?

5

@filipa Ok, here is another attempt at it. The only thing I see wrong with this one is that if the event is a single day Saturday event, then on Sunday of that weekend the logical flag will remain True because the event did occur during the weekend but it ended on the Saturday of the weekend.

={"ThisWeekEnd";ARRAYFORMULA(IF(ISBLANK(A2:A),,
 IF(B2:B>=(IF(WEEKDAY(TODAY())=1,TODAY(),TODAY()-WEEKDAY(TODAY(),3)+IF(WEEKDAY(TODAY(),3)>5,11,6))),
 IF(A2:A<=(IF(WEEKDAY(TODAY())=1,TODAY(),TODAY()-WEEKDAY(TODAY(),3)+IF(WEEKDAY(TODAY(),3)>5,11,6))),TRUE,FALSE),
 IF(B2:B<(IF(WEEKDAY(TODAY())=1,TODAY()-1,TODAY()-WEEKDAY(TODAY(),3)+IF(WEEKDAY(TODAY(),3)>5,11,5))),FALSE,TRUE))))}
1 Like
6

Worked perfectly! Thank you so much!!