MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Calculate Nonstandard Work Weeks


July 24, 2019 - by Bill Jelen

Calculate Nonstandard Work Weeks. Photo Credit: Ramón Salinero at Unsplash.com

In my live Power Excel seminars, it is pretty early in the day when I show how to right-click the Fill Handle, drag a date, and then choose Fill Weekdays. This fills Monday through Friday dates. I ask the audience, “How many of you work Monday through Friday?” A lot of hands go up. I say, “That’s great. For everyone else, Microsoft clearly doesn’t care about you.” Laughter.

It certainly seems that if you work anything other than Monday through Friday or have a year ending any day other than December 31, a lot of things in Excel don’t work very well.

However, two functions in Excel show that the Excel team does care about people who work odd work weeks: NETWORKDAYS.INTL and WORKDAY.INTL.

But let’s start with their original Monday–Friday antecedents. The following figure shows a start date in column B and an end date in column C. If you subtract =C5-B5, you will get the number of days between the two dates. To figure out the number of weekdays, you use =NETWORKDAYS(B2,C2).


Start dates in column B and End Dates in column C. To calculate the number of Monday to Friday dates, use =NETWORKDAYS(B2,C2).

Note

If you subtract Monday August 14 from Friday August 18, Excel will tell you that there are 4 days between the two dates. Excel does not count the end date. But, NETWORKDAYS counts both the first and last date in the range.

It gets even better. NETWORKDAYS allows for an optional third argument where you specify work holidays. In the next figure, the list of holidays in H3:H15 allows the Work Days Less Holidays calculation in column F.

To ignore holiday dates, enter a list of holiday dates on the worksheet. In this case, H3:H15 contains 5/28/2018 for Memorial Day through 5/27/2019 for Memorial Day next year. Adjust the formula to =NETWORKDAYS(B2,C2,$H$3:$H$15).

Prior to Excel 2007, the NETWORKDAYS and WORKDAY functions were available if you enabled the Analysis ToolPak add-in that shipped with every copy of Excel. For Excel 2007, those add-ins were made a core part of Excel. In Excel 2007, Microsoft added INTL versions of both functions with a new Weekend argument. This argument allowed for any two consecutive days as the weekend and also allowed for a one-day weekend.

For different weekends, use =NETWORKDAYS.INTL. When you get to the third argument, a list of possible weekends appears. Use 1 for Saturday & Sunday. 2 is Sunday & Monday. 7 is Friday & Saturday. 11 is Sunday only. 17 is Saturday only.

Plus, there are several countries with weekends that don’t fall on Saturday and Sunday. All of the countries shown below except Brunei Darussalem gained functionality with NETWORKDAYS.INTL and WORKDAY.INTL.

However, there are still cases where the weekend does not meet any of the 14 weekend definitions added in Excel 2007.

For example, the Hartville Marketplace is open Monday, Thursday, Friday, and Saturday. That means their weekend is Tuesday, Wednesday, and Sunday.

Starting in Excel 2010, instead of using 1-7 or 11-17 as the weekend argument, you can pass 7-digit binary text to indicate whether a company is open or closed on a particular day. It seems a bit unusual, but you use a 1 to indicate that the store is closed for the weekend and a 0 to indicate that the store is open. After all, 1 normally means On and 0 normally means Off. But the name of the argument is Weekend, so 1 means it is a day off, and 0 means you don’t have the day off.

Thus, for the Monday, Thursday, Friday, Saturday schedule at the Hartville Marketplace, you would use "0110001". Every time I type one of these text strings, I have to silently say in my head, “Monday, Tuesday, Wednesday…” as I type each digit.

Marion Coblentz at the Hartville Marketplace could use the following formula to figure out how many Marketplace days there are between two dates.

The tooltip for NETWORKDAYS.INTL does not describe this, but you can specify a 7-digit binary string for the weekend argument. This screenshot describes a farmers market that is open Monday, Thursday, Friday, and Saturday. Thus, the weekend is Tuesday, Wednesday, and Sunday. The 7 digits in the binary string start with Monday and run through Sunday. Type a zero if it is a workday and a 1 if they are closed. The formula is =NETWORKDAYS.INTL(A8,B8,"0110001").

By the way, I did not use the optional Holidays argument above because Memorial Day, July 4, and Labor Day are the biggest customer days in Hartville.

If you are ever in northeastern Ohio, you need to stop by Hartville to see the 100% American-Made house inside of the Hartville Hardware and to try the great food at the Hartville Kitchen.

Title Photo: Ramón Salinero at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.