Checking if a date falls within a range

Tiffany McKerahan

Hi, I've seen a few answers on this forum that addressed similar questions to my own but I can't seem to make those solutions work for me so I thought I would ask again 😁

I have a time off request form populating a worksheet and I'd like the worksheet to automatically calculate if a time off request falls within 3 business days of a company holiday.

So here's my source worksheet, populated by the form:

And here's my helper sheet with holiday info; I used WORKDAY for the start and end ranges.

So I'm not sure how to marry this up and see if either the Start Date or the End Date from my source worksheet fall within the Start Range and/or End Range from the holiday info helper sheet and then populate Y or N in the Holiday Warning column of the source sheet. Your advice would be much appreciated!

Thanks,

Tiffany

Mike Wilday

Tagging this post to see if anyone in this community has a way to make this work.

Tiffany McKerahan

Crickets...I can't believe that I've stumped the Smartsheet community! Does anyone have any alternative methods in which I might be able to accomplish the same task? I'd appreciate any ideas that anyone may have. Thanks!

Adriana Apodaca

Morning Tiffany,

I had a similar issue with identifying staff on-call vs time off that overlapped. After looking at many pages of discussions, I set up a conflict column (your Holiday Warning). Here is the article I worked off of. Hope this helps! But I am also interested if there is an easier way!

https://community.smartsheet.com/discussion/comment/241427#Comment_241427

Ian Cooper

@Tiffany McKerahan I had a similar issue and came across this thread:

https://community.smartsheet.com/discussion/69159/lookup-function-check-if-date-falls-within-a-range-and-return-a-corresponding-value-same-row

I see this is from back in 2020, so I'm sure you have it fixed now. Was adding to this thread to possibly help the next person.