Calculate working days between 2 dates
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 NETWORKDAYS All you have to do is to enter the start and end dates into the formula. The NETWORKDAYS 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 holiday list as the third part of the formula (must be entered as a range) . You can input all the holidays for the year and the NETWORKDAYS formula will be able to pick the relevant holidays between the start and end dates. This formula is readily available in Excel 2007 and 2010. If you are using Excel 2003 and below, you must activate the Analysis Toolpak Addin to add this formula into Excel. This Addin contains extra powerful formulas, just like the NETWORKDAYS formula, that are unknown to most users.
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:
SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)<=5))
SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)<=5))
Given below is the explanation on how the formula works.
SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)<=5))
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))
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))
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.
SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)=5))*0.5
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)<=5))+SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)=6))*0.5
=SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT($D6&”:”&$E6)),2)<=5))+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.
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
