Check if a date is within multiple ranges

Yann Finger

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:

1. One where I book people's time to work on specific tasks

1. 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

Paul Newcome

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")

Paul Newcome

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")

Yann Finger

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")

Paul Newcome

Happy to help and glad you were able to tweak it to suit your exact needs. 👍️