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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!