How to use the collect formula on a date range?

Elissa A

Hey!

I've been trying to figure out how to return a value from another sheet if a date falls within a range. I thought I had the formula figured out but it's not working 100% of the time, so I was wondering if anyone had suggestions on what to do?

To start, I'll explain the goal. I need to have a "Y" value populated in a cell if someone has taken time off on a specific date. There is a team calendar that shows the start date and end date for PTO that looks like this (grid view).

I need it to populate a value in the cells on this sheet.

The formula that works sometimes is this:

=IF(CONTAINS("Anoop", JOIN(COLLECT({FIS - Team Calendar Range 1}, {FIS - Team Calendar Range 2}, >=[Monday Date]@row, {FIS - Team Calendar End Date}, <=[Monday Date]@row))), "y")

I tried to set it up to collect all of the "Employee" column values from the team calendar when that day's date falls between the "Start Date" column and the "End Date" column. Then the formula checks to see if that persons name is one of those values, and returns a "y" if it is.

The formula works for single day PTO entries, but none of the entries that are longer than one day. Is there a way to check if that date falls within the range? Or is there a better way to write this formula?

Thanks for your help!

Paul Newcome

Can you outline what each of your ranges are?

Elissa A

Hi @Paul Newcome

The date range is the start date column to end date column. Is this approach possible?

Thanks!

Elissa

Paul Newcome

What are each of the ranges covering exactly?

Range 1 = ?

Range 2 = ?

End Date = ?

Elissa A

Range 1 = Employee column

Range 2 = Start Date

Range 3 = End Date

Paul Newcome

Try this:

=IF(COUNTIFS({Employee Column}, FIND("Anoop", @cell)> 0, {Start Date Column}, @cell<= [Monday Date]@row, {End Date Column}, @cell>= [Monday Date]@row)> 0, "Y")

Elissa A

That worked perfectly. Thanks so much for your help!

Paul Newcome

Happy to help. 👍️