Calculate Working Days Between 2 Date

Calculating working days is always a challenge for all of us. Though the concept is simple, calculating it has never been easy. The approach we commonly use is to physically count the number of days using a calendar. This approach is very prone to human error and therefore, we have a tendency to count a few more times to make sure that we have counted correctly. Imagine how taxing it is to do the count for numerous sets of dates. Things would have been different if you are using MS Excel, because it can help you get the answers quickly and accurately in a few different ways.

The first way is to make use of the formula called NETWORKDAYS. The formula is very simple to use. All you have to do is to type in the formula, enter the start and end dates. The formula will immediately display the number of working days between the 2 given dates, based on a five days work week. If you wish to exclude holidays in the calculation, just enter the holidays you wish to exclude in the formula. You can input all the holidays for the year and the NETWORKDAYS formula will be able to pick the relevant holidays as and when they fall between the start and end dates. You may not find this formula in your Excel application, if you did not activate the Analysis Toolpak Addin. Some of you may have to install the Addin into Excel. This Addin contains extra powerful formulas, that is unknown to most users, just like the NETWORKDAYS formula.

The second way is to calculate working days is to use a combination of formulas as shown (D6 contains the start date and E6 the end date:

SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)

It makes use of the power of array formulas. The INDIRECT formula converts a text reference into a valid range reference.

SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)ROW(INDIRECT($D6&”:”&$E6)),2)WEEKDAY formula to all the dates listed (invisibly) and identify those dates that meet the condition(s) mentioned (if the dates are earlier or the same as Friday, i.e. Mon to Fri).

SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)SUMPRODUCT formula, it will add up all the ones and zeros found in the whole range of dates in the invisible table and give you the total number of days that are from Monday to Friday in each week.

Using the same formula but changing the criteria from SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)=5))*0.5

When the 2 formulas are added together, we will be able to calculate the number of working days based on a 5.5 day work week.

=SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)=6))*0.5

We have made use of the second formula in our payroll template to calculate the number of working days in a month.