This page describes a few methods for working with intervals of dates. Specifically, it address the questions of whether a date falls within an interval, the number of days that two intervals overlap, and the how many days are in one interval, excluding those days in another interval. These formulas can be quite useful for scheduling applications, such as employee vacation schedules.
Is A Date In An Interval?
Suppose we have 3 dates -- a start date, and end date, and a test date. We can test whether the test date falls within the interval between start date and end date. In this formula, we will use three named cells:
TDate1 for the start date, TDate2 for the end date, and TDate for the test date. This formula will return either TRUE or FALSE, indicating whether TDate falls in the interval.=AND((TDate>=MIN(TDate1,TDate2)),TDate<=MAX(TDate1,TDate2))
For example if TDate1 is 1-Jan and TDate2 is 31-Jan , and TDate is 15-Jan , the formula will return TRUE, indicating that TDate falls in the interval.
In this formula, it does not matter whether TDate1
is earlier or later than TDate2.
Number Of Days In One Interval And Not In Another
We can also work with two date intervals, and determine the number of days that fall in one interval, and not in another. This can become complicated because of how the intervals may overlap. For example, the main interval may complete contain the exclusion interval. Or, the exclusion interval may completely contain the main interval. Moreover, only part of the main interval may be contained within the exclusion interval, either at the starting or the ending end of the interval. Finally, the two intervals may not overlap at all.
Below is a summary of the various interval types. The Dates values are the days we wish to count. The VDates values are the days we wish to exclude from the Dates interval. The complexity of the formula is due to the fact that it must handle all of the interval types.
For this formula, we will have 4 named cells, as shown below:
| Name | Description |
| Date1 | The starting date of the main interval. The main interval is the dates we want to work count. |
| Date2 | The ending date of the main interval. |
| VDate1 | The starting date of the exclusion interval. The exclusion interval is the dates that we want to exclude from the count of the main interval. |
| VDate2 | The ending date of the exclusion interval. |
| NWRange | A list of holiday dates. Used in the second version of the formula, which uses the NETWORKDAYS function. |
For this formula, we require that Date1 is less than (earlier than) or equal to Date2, and that VDate1 is less than (earlier than) or equal to VDate2.
=IF(OR(Date2<VDate1,VDate2<Date1),Date2-Date1+1,IF(OR(AND(Date1<=VDate1,Date2>=VDate2),
AND(Date1>=VDate1,Date2<=VDate2)),MAX(0,(Date2-Date1)-(VDate2-VDate1)),
IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND(Date1>=VDate1,Date2>VDate2)),
MAX(0,(VDate1-Date1))+MAX(0,Date2-VDate2),NA())))
Here are some examples.
| Date1 | Date2 | VDate1 | VDate2 | Result | Description |
| 1-Jan | 31-Jan | 10-Jan | 20-Jan | 20 | There are 20 days between 1-Jan and 9-Jan (9 days) and 21-Jan and 31-Jan (11 days). The 11 days between 10-Jan and 20-Jan are subtracted from the 31 days between 1-Jan and 31-Jan. In this example, the entire exclusion interval (the VDates) is included within the main interval (the Dates). |
| 10-Jan | 20-Jan | 1-Jan | 31-Jan | 0 | Here, the entire main interval is included within the exclusion interval. There are no days between 10-Jan and 20-Jan that do fall outside the 1-Jan and 31-Jan. |
| 1-Jan | 15-Jan | 10-Jan | 20-Jan | 9 | In this case, the ending segment of the main interval (1-Jan to 15-Jan) overlaps with the beginning segment of the exclusion interval (10-Jan to 20-Jan). There are 9 days (1-Jan to 9-Jan) in the main interval that do not overlap with the exclusion interval. |
| 10-Jan | 20-Jan | 1-Jan | 15-Jan | 5 | Ending segment of the main interval overlaps the exclusion interval. There are 5 days (16-Jan to 20-Jan) in the main interval that are not included in the exclusion segement. |
Note that the dates here are inclusive. There are 10 days between 1-Jan and 10-Jan. This is one day different that what you would get from simply subtracting the dates.
The formula above does not treat weekend days differently from working days. In other words, Saturdays and Sundays are included in the calculations. If you want to count only weekdays, excluding weekends and holidays, use the modified version below, which calls the NETWORKDAYS function to compute the number of working days in the intervals. This function adds another name ranged to the mix. This name, NWRange, refers to a range containing a list of holidays. If you do not use holidays, you can either point this name to an empty cell, or eliminate it from the formula entirely.
=IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS(Date1,Date2,NWRange),
IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND(Date1>=VDate1,Date2<=VDate2)),
MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS(VDate1,VDate2,NWRange)),
IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND(Date1>=VDate1,Date2>VDate2)),
IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))+
IF((Date2<=VDate2),0,NETWORKDAYS(VDate2+1,Date2,NWRange)),NA())))
The NETWORKDAYS
function is part of the Analysis Tool Pack Add-In, so you
must have this module installed in order to use this formula. For more
information about using formulas to create the dates of holidays, see the Holidays
page.
Tangent: The reason the named cells are VDate1
and VDate2 is that I
originally wrote this formula for a Vacation timekeeping application, and the V
refers to "Vacation". Of course, you can name your cells
anything that works with your application, or you can simply use cell
references.
Number Of Days Common To Two Intervals
The previous section worked with a logical NOT condition -- dates in one interval and NOT in another. This section describes a formula for working with the inverse of that -- the number of days that are in BOTH of two intervals.
For this formula, we will have 4 named cells, as shown below:
| Name | Description |
| IDate1 | The starting date of the first interval. |
| IDate2 | The ending date of the first interval. |
| RDate1 | The starting date of the second interval. |
| RDate2 | The ending date of the second interval. |
| NWRange | A list of holiday dates. Used in the second version of the formula, which uses the NETWORKDAYS function. |
For this formula, we require that IDate1 is less than (earlier than) or equal to IDate2, and that RDate1 is less than (earlier than) or equal to RDate2. The formula below will return the number of days that are in both intervals.
=IF(OR(IDate2<RDate1,IDate1>RDate2),0,(MIN(IDate2,RDate2)-MAX(IDate1,RDate1)+1))
Here are some examples.
| IDate1 | IDate2 | RDate1 | RDate2 | Result | Description |
| 1-Jan | 31-Jan | 10-Jan | 20-Jan | 11 | There are 11 days common to the intervals. Since the RDates are contained within the IDates, the result is the number of days between 10-Jan and 20-Jan, or 11 days. |
| 10-Jan | 20-Jan | 1-Jan | 31-Jan | 11 | Since this is an AND condition format, we can reverse the dates between IDates and RDates, and get the same result as above, 11 days. |
| 1-Jan | 15-Jan | 10-Jan | 20-Jan | 6 | Here, there are 6 days common to the two intervals -- the dates 10-Jan to 15-Jan fall in both intervals. |
| 1-Jan | 10-Jan | 15-Jan | 20-Jan | 0 | The result here is 0, because there are no dates in the IDate interval (1-Jan to 10-Jan) than fall in the RDate interval (15-Jan to 20-Jan). |
Note that the dates here are inclusive. There are 10 days between 1-Jan and 10-Jan. This is one day different that what you would get from simply subtracting the dates.
The formula above does not treat weekend days differently from working days. In other words, Saturdays and Sundays are included in the calculations. If you want to count only weekdays, excluding weekends and holidays, use the modified version below, which calls the NETWORKDAYS function to compute the number of working days in the intervals. This function adds another name ranged to the mix. This name, NWRange, refers to a range containing a list of holidays. If you do not use holidays, you can either point this name to an empty cell, or eliminate it from the formula entirely.
=IF(OR(IDate2<RDate1,IDate1>RDate2),0,
ABS(NETWORKDAYS(MIN(IDate2,RDate2),MAX(IDate1,RDate1),NWRange)))
The NETWORKDAYS
function is part of the Analysis Tool Pack Add-In, so you
must have this module installed in order to use this formula. For more
information about using formulas to create the dates of holidays, see the Holidays
page.