Distributing Days Over Intervals

Excel is frequently used as a platform to build scheduling applications. In these sort of applications, it may be useful to determine how the period between the start date and the end date are distributed across the intervening months or years. For example, if a project starts on 12-February and ends on 28-June, how many days are used in each month? And what about working days (Monday through Friday)? Here, we'll look at a few formulas that will allow you do write these sorts of schedules. NOTE: Some of these formulas use the NETWORKDAYS function, which returns the number of working days between two dates. This function is part of the Analysis Tool Pack, so you must have this installed in order to use these formulas. For more information about installing the ATP, click here. Distributing Working Days Across Months You can distribute the number of working days between two dates over the intervening months. See the table below, for an example.

	This shows how the number of dates between the Start Date in column B and the End Date in column C are distributed over the months of the year. For the dates show in row 6, 15-Jan-00 and 12-Jun-00, the number of working days for each month in that interval are shown columns D through O. The formula which computes these days is =MAX(0,NETWORKDAYS(MAX(D$5,$B6),MIN(DATE(YEAR(D$5),MONTH(D$5)+1,0),$C6))) This formula is entered in D5, and then we use Fill Down and Fill Right to fill it through O7. It is important to use the relative and absolute cell references (the $ in the cell names) so that when the formula is copied down and to the right, the proper cells are referenced. If you are not familiar with this type of cell referencing, refer to Relative And Absolute Addressing. The cells in D5:O5, which appear as month names, are actually real Excel dates, representing the first day of that month. For example, D5 actually contains the date 1-Jan-2000, but is formatted only to show "Jan". The formula requires these cells to actual dates. (You can enter Jan 2000 in a cell, and Excel will automatically convert it to the serial date of the first day of that month and year). To see how this formula works, let's break it apart. For any month, we first find the larger (later) of the Start Date and the first day of the month, with MAX(D$5,$B6). This will always return the first day of the month (D$5) except for the first month, in which case it will be the Start Date. Then, we find the last day of the month. That's what DATE(YEAR(D$5),MONTH(D$5)+1,0) does, by taking into account that the last day of one month is the 0th day of the next month. Next, we use the MIN function to find the smaller of the last day of the month and the End Date. That's what MIN(DATE(YEAR(D$5),MONTH(D$5)+1,0),$C6) does. For every month but the last month, this will always be the last day of the month. Then, we use the NETWORKDAYS function to return the number of working days between these two dates (between either the Start Date or the first of the month, and either the End Date of the last of the month). NETWORKDAYS(MAX(D$5,$B6),MIN(DATE(YEAR(D$5),MONTH(D$5)+1,0),$C6)) Since NETWORKDAYS will return a negative number for months that are not included between Start Date and End Date at all, we simply wrap the entire function in a MAX function, to return a zero if the result of NETWORKDAYS is less that zero. =MAX(0,NETWORKDAYS(MAX(D$5,$B6),MIN(DATE(YEAR(D$5),MONTH(D$5)+1,0),$C6))) All of the functions on this page work in essentially the same way, with some minor variations. I'm not going to describe each of the others in as much detail. Remember, the NETWORKDAYS function can also take into account a list of holidays to exclude from the count. Suppose you had a list of holidays in T1:T20, you could exclude these from the result by changing the formula to: =MAX(0,NETWORKDAYS(MAX(D$5,$B6),MIN(DATE(YEAR(D$5),MONTH(D$5)+1,0),$C6),$T$1:$T$20)) See the online help for NETWORKDAYS to learn more about this. You can have more than the 12 months as shown in the example, and the first month does not need to be January. For example if your Start Date is in June-2000, and your end date is in March-2003, you start with June-2000 in D5 and extend that our for 34 columns to AK5, which would contain March-2003. Nothing in the formula requires 12 or fewer months, or that the dates be in the current year. It will work properly for any number of months (up to 256, which is the limit of the number of columns in Excel). Distributing All Days Across Months In the previous section, we used the NETWORKDAYS function to count only working days. To count all of the days between the Start Date and End Date, including weekends and holidays, simply use =MAX(0,(MIN($C17,DATE(YEAR(D$16),MONTH(D$16)+1,0))-MAX($B17,D$16)+1)) instead of the the function in the previous section. This will count all of the days, not just the working days. See the sample data below for an example:

This formula works essentially the same as the formula in the previous section, but uses subtraction instead of NETWORKDAYS to count the number of days. You can have more than the 12 months as shown in the example, and the first month does not need to be January. For example if your Start Date is in June-2000, and your end date is in March-2003, you start with June-2000 in D5 and extend that our for 34 columns to AK5, which would contain March-2003. Nothing in the formula requires 12 or fewer months, or that the dates be in the current year. It will work properly for any number of months (up to 256, which is the limit of the number of columns in Excel). Distributing Working Days Across Years The previous sections explained how to distribute the days in an interval over a number of months. You can also do something similar to distribute the working days between Start Date and End Date over years, instead of months. In the example below, the interval between Start Date and End Date is distributed of the years 2000 to 2002 and 2003 and 2009.

This formula works in a similar manner to the formula in the first section. However, the year values in D5 to O5 are not Excel serial dates. The actual value of the cell is the year number. For example, D27 contains the number 1000. The formula used to distribute the days over the years is =MAX(0,NETWORKDAYS(MAX(DATE(D$27,1,1),$B28),MIN(DATE(D$27,12,31),$C28))) This formula works for each year by first determining the larger (later) of the Start Date ($B28) and the first day of the year with the function MAX(DATE(D$27,1,1),$B28). This will be the first day of the year for all years except the first. Then, it determines the earlier (smaller) of the the End Date and the last day of the year with the function MIN(DATE(D$27,12,31),$C28)). This will be the last day of the year for all years except the last. Next, it uses the NETWORKDAYS function to determine the number of working days between the these two dates: NETWORKDAYS(MAX(DATE(D$27,1,1),$B28),MIN(DATE(D$27,12,31),$C28)) Since NETWORKDAYS may return a negative number, we wrap the entire function in a MAX to return a zero if NETWORKDAYS returns a negative number: =MAX(0,NETWORKDAYS(MAX(DATE(D$27,1,1),$B28),MIN(DATE(D$27,12,31),$C28))) Distributing All Days Across Years Finally, you may want to distribute all days, not just the working days, across a number of years. The formula to do this is =MAX(0,(MIN($C39,DATE(D$38,12,31))-MAX($B39,DATE(D$38,1,1))+1)) as shown in the following example.

This formula works essentially the same as the formula in the previous section, but uses subtraction instead of NETWORKDAYS to count the number of days.