CODEWITHSUNDEEP
excel
JDOE
JDOE

Reputation: 13

How to know if a date is between a list of start/end dates?

I have a table where I put my holidays that way:

| Start date | End date   | Reason                 |
+------------+------------+------------------------+
| 01/02/2018 | 15/02/2018 | Trip to somewhere nice |
| 07/04/2018 | 08/04/2018 | Sick days              |
...

I have another table with a list of dates

| Date     | Holiday? |
+----------+----------+
|03/02/2018| YES      |
|20/02/2018| NO       |
|08/04/2018| YES      |
...

My question: how can I write a formula in the Holiday? column to know if I was on holiday on the date on the left? All the dates are in the Date type.

Upvotes: 1

Views: 389

Answers (2)

Vityata
Vityata

Reputation: 43585

The easiest way is to use =IF(OR(AND)) conditions and to set it up a bit manually like this:

=IF(OR(
      AND((inspectedDate>=startDate1),inspectedDate<=endDate1)),
      AND((inspectedDate>=startDate2),(inspectedDate<=endDate2))
       ),"Holiday","")
  • The AND part of the formula makes sure that the inspectedDate is within a holiday range
  • The OR part returns True, if it is within one of the ranges.

Upvotes: 0

Gordon
Gordon

Reputation: 1165

You can use countifs

to count if the date in the lefthand $B$3:$B$4 column is less than or equal to the date you want to check B9 and count it the date in the righthand column $C$3:$C$4 is greater than or equal to the test date B9.

=COUNTIFS($B$3:$B$4,"<="&B9,$C$3:$C$4,">="&B9)

this returns 0 if it's not within any of the dates or 1,2,3.. if it is. So add an if statement to retun Yes or No.

=IF(COUNTIFS($B$3:$B$4,"<="&B9,$C$3:$C$4,">="&B9)>0,"YES","NO")

Upvotes: 1

Related Questions