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. As this approach is very prone to human error, we are likely 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 a simple formula called**The second way** to calculate working days is to
use a combination of formulas as shown (D6 contains the
start date and E6 the end date:

Given below is the explanation on how the formula works.

SUMPRODUCT(--(WEEKDAY(ROW(**INDIRECT($D6&":"&$E6)**),2)<=5))

The INDIRECT formula (in bold) converts a range entered as a text (in our example $D6&":"&$E6) into a valid range reference. This means that the formula INDIRECT($D6&":"&$E6) will be converted into a valid range such as 41275:41288. The number 41275 (located in cell D6) is the start date presented in number format and 41288 (located in cell e6) is the end date. The formula actually mimic the selection of the entire rows from 41275 to 41288.

What is the purpose of the ROW formula?

SUMPRODUCT(--(WEEKDAY(**ROW(INDIRECT($D6&":"&$E6))**,2)<=5))

The main purpose of the ROW formula is to return the row number of a cell. When you click on the ROW formula and select the cell F8, the ROW formuals returns the value 8. In our example, the ROW formula has the ability of listing down the rows from 41275 to 41288 virtually.

SUMPRODUCT(--(**WEEKDAY(ROW(INDIRECT($D6&":"&$E6)),2)**<=5))

The WEEKDAY formula that embraces the ROW and INDIRECT formula is meant to identify the day of the week for the numbers
(in the form of dates). See
**WEEKDAY** formula. The setting 2
is a code to determine how the each day of the week is returned. When the code 2 is used, the WEEKDAY formula will
treat Monday as 1, Tueday as 2 until Sunday which is presented as 7. When the WEEKDAY formula embraces the ROW formula, it is
made to run through all the dates from 41275 to 41288 and list down virtually the days of the week.

The less than or equal to 5 (<=5) is meant to identify the dates that fall into Monday to Friday. If the date is truly one of the weekdays, then it will return true.

The two minus signs (--) is called a "double unary". The first minus sign converts the TRUE an FALSE found in the weekday formula into ones and zeros while the second minus sign will help to convert all the results into positive numbers.

Finally, by embracing the entire WEEKDAY formula within the
**SUMPRODUCT** formula, the completed formula can help to
add up all the
ones (when the date is a weekday) and zeros (when the date is a weekend)
presented virtually and give you the
total number of weekdays (from Monday to Friday) between the start and end date.

Using the same formula but changing the criteria from <=5 to 6, we are able to count the number of Saturdays in between the 2 dates. Since Saturday is counted as half day working day, we have to multiply the formula by 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.

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

•

## New! Comments

Have your say about what you just read! Leave me a comment in the box below.