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 

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. 

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.

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 

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.