Check if a date is within multiple ranges
Hi there,
I'm trying to figure out whether I could write a formula to check if a given date is within multiple ranges.
Concretely, I have two sheets:
- One where I book people's time to work on specific tasks
- Another where I keep track of people's holidays
What I would like is to get an error message if I try to book Yann Finger (for example) in the first sheet during any of his holidays (saved in the second sheet). I was first thinking of using VLOOKUP to check within a range, but the problem I'm encountering is that Yann has booked multiple holidays so I need to check multiple ranges (and I believe that VLOOKUP would only return the first range).
Does anyone know if that is possible and what the formula would be? or maybe a workaround? Thanks!
Best,
Yann
Best Answer
-
Try something like this...
=IF(COUNTIFS({Holiday Sheet Start Date}, @cell <= Start@row + [Day Quantity]@row, {Holiday Sheet End Date}, @cell >= Start@row) > 0, "ERROR MESSAGE OF CHOICE")
Answers
-
Try something like this...
=IF(COUNTIFS({Holiday Sheet Start Date}, @cell <= Start@row + [Day Quantity]@row, {Holiday Sheet End Date}, @cell >= Start@row) > 0, "ERROR MESSAGE OF CHOICE")
-
You're a star @Paul Newcome !
I made a small change to it to check based on staff but the logic worked flawlessly :)
=IF(COUNTIFS({Staff holidays - start date}, @cell <= Start@row, {Staff holidays - End date}, @cell >= Start@row, {Staff holidays - Staff}, @cell = Staff@row) > 0, "! " + Staff@row + " is on holiday")
-
Happy to help and glad you were able to tweak it to suit your exact needs. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!