Calculating working days is always a challenge for all of us. 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 so we tend to count a few more times to make sure we have counted correctly. Imagine how tiring it is to count numerous sets of dates. Things would have been different if you were using MS Excel as it can help you get the answers quickly and accurately in 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 write the formula, enter the start and end dates. The formula will immediately display the number of business days between the 2 given dates, based on a five-day business week. If you want to exclude holidays in the calculation, simply enter the holidays you want to exclude in the formula. You can enter all the holidays of the year and the NETWORKDAYS formula will be able to choose the relevant holidays as they fall between the start and end dates. You may not find this formula in your Excel application if you have not activated the Analysis Toolpak add-in. Some of you may have to install the add-in in Excel. This plugin contains extra powerful formulas, which are unknown to most users, just like the NETWORKDAYS formula.
The second way to calculate business days is to use a combination of formulas as shown (D6 contains the start date and E6 the end date:
SUMPRODUCT(–(DAY OF WEEK(ROW(INDIRECT($D6&”:”&$E6)),2)
It makes use of the power of array formulas. The INDIRECT formula converts a text reference to a valid range reference.
SUMPRODUCT(–(DAY OF WEEK(ROW(INDIRECT($D6&”:”&$E6)),2)ROW(INDIRECT($D6&”:”&$E6)),2) WEEKDAY formula for all listed dates (invisibly) and identify those dates that meet the listed conditions (if the dates are before or equal to Friday, ie Monday through Friday).
SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)), 2)SUMPRODUCT formula, will add all the ones and zeros that are 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, we will be able to calculate the number of working days based on a 5.5 day work week.
=SUMPRODUCT(–(DAY OF WEEK(ROW(INDIRECT($D6&”:”&$E6)),2)SUMPRODUCT(–(DAY OF WEEK(ROW(INDIRECT($D6&”:”&$E6)),2)=6))*0.5
We have used the second formula in our payroll template to calculate the number of business days in a month.