How to use the collect formula on a date range?


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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!