Calculate working days between 2 dates

Calculating working days is always a challenge for everyone. Although 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 tend to count a few more times to make sure we have counted correctly. Imagine how difficult it is to count numerous sets of dates. Things would have been different if you were using MS Excel, because it can help you get the answers quickly and accurately in different ways.

The first way is to make use of the formula called NETWORK DAYS. The formula is very easy to use. All you have to do is write 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-day working week. If you want to exclude holidays in the calculation, just enter the holidays you want to exclude in the formula. You can enter all holidays for the year and the NETWORKDAYS formula will be able to choose the relevant holidays 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 add-in. Some of you may have to install the add-in in Excel. This plugin contains extra powerful formulas, which most users are unaware of, just like the NETWORKDAYS formula.

The second way is to calculate the 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)

SUMPRODUCT (- (DAY OF THE WEEK (ROW (INDIRECT ($ D6 & “:” & $ E6)), 2) ROW (INDIRECT ($ D6 & “:” & $ E6)), 2) WEEKDAY formula for all the dates listed (invisibly) and identify those dates that meet the aforementioned conditions (if the dates are before or equal to Friday, that is, from Monday to Friday).

SUMPRODUCT (- (WEEKDAY (ROW (INDIRECT ($ D6 & “:” & $ E6)), 2)SUMPRODUCT formula, will add all the ones and zeros found in the entire date range in the invisible table and give you the total number of days that are Monday through Friday in each week.

Using the same formula but changing the criteria of 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 working week.

= SUMPRODUCT (- (DAY OF THE WEEK (ROW (INDIRECT ($ D6 & “:” & $ E6)), 2)SUMPRODUCT (- (DAY OF THE WEEK (ROW (INDIRECT ($ D6 & “:” & $ E6)), 2) = 6)) * 0.5

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