Calculating business 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 therefore we tend to count a few more times to make sure we have counted correctly. Imagine how exhausting 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 in the year and the NETWORKDAYS formula will be able to pick 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 most of the users are unaware of, 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) formula WEEKDAY for all listed dates (invisibly) and identify those dates that meet the listed conditions (if the dates are before or equal to Friday, i.e. Monday to Friday).
SUMPRODUCT(–(DAY OF WEEK(ROW(INDIRECT($D6&”:”&$E6)),2)Formula SUMPRODUCT, will add up 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 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.