Date Intervals
|
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:
|
||||||||||||||||||||||||||||||||||||||||||||||||||
|
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), Here are some examples.
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||
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), 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. 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:
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.
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,
|
||||||||||||||||||||||||||||||||||||||||||||||||||
| A final note: These formulas are designed to illustrate the concept of the date "intervals", and how they need to be handled by the Excel date arithmetic. Logicians can probably reduce the formulas to simpler forms by consolidating the ANDs and ORs. But the point here is illustration and education, not the simplest possible formula. | ||||||||||||||||||||||||||||||||||||||||||||||||||
|
Other Date
And Time Related Procedures are described on the following pages. |
|||
| Holidays |
|
||