MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Calculate Workdays

September 11, 2017 - by Bill Jelen

Calculate Workdays

How many workdays between a start date and an end date? The old functions work for Monday-Friday workweeks, but there are new options for odd work weeks, even Monday, Thursday, Friday, Saturday.

In my live Power Excel seminar, 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 B and an end date in C. If you subtract =C5-B5, you will get the number of days elapsed between the two dates. To figure out the number of weekdays, you would use =NETWORKDAYS(B2,C2).


It gets even better. The old 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.

Work Days Less Holidays Calculation
Work Days Less Holidays Calculation

Prior to Excel 2007, the NETWORKDAYS and WORKDAY function 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 part of the core Excel. 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.


I’ve seen a manufacturing plant switch to six day weeks in order to meet excess demand.

6 Day Weeks
6 Day Weeks

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

Country Weekends
Country Weekends

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

I happen to live in the same county as the Pro Football Hall of Fame in Canton, Ohio. But the top tourism destination in our county is not the hall of fame. The top tourism destination is the Hartville Marketplace and Flea Market. Started in 1939, this place is a hot spot for people looking for fresh produce and bargains. The original lunch stand became the Hartville Kitchen restaurant. And the nearby Hartville Hardware is so big, they built an entire house inside the hardware store. But the Marketplace is the beneficiary of the new, secret weekend argument for NETWORKDAYS and WORKDAY. The 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 a 7-digit binary text to indicate if 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.

Marketplace Days between Two Dates
Marketplace Days between Two Dates

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. 

Watch Video

  • Date math in Excel: Subtract earlier date from later date + 1
  • To ignore weekends, use NETWORKDAYS function
  • To not count holidays, use the 3rd argument in NETWORKDAYS
  • For non-standard weekends, use NETWORKDAYS.INTL
  • Secret 7-binary digit code for work weeks that are not consecutive days
  • Alt + E S F for Paste Special Formulas

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2023 calculate workdays even for
  • non-standard work weeks well I'll be
  • podcasting this entire book click the
  • eye on the top right hand corner to get
  • to the playlist hey welcome back to the
  • MrExcel netcast I'm Bill Jelen so I
  • talked about this feature back in
  • episode 1977 for Phil weekdays you right
  • click the fill handle drag and when you
  • let go you choose fill weekdays and you
  • see that it fills monday through friday
  • works works great for a lot of countries
  • in the world but there's countries in
  • the world where the weekdays the weekend
  • is not saturday sunday right and just
  • sorry Microsoft Excel doesn't care about
  • you alright so we're gonna talk about
  • how to calculate the number of work days
  • between two dates and we just need to
  • know the number of days between two
  • dates we take the later date minus the
  • earlier date plus one and you'll see how
  • many work days there are right that
  • works great if you work everyday right
  • but if you want to leave out the
  • weekends this areas in Sunday's equal
  • net work days has been around while in
  • the analysis tool back back you know a
  • long time it became an official part of
  • excel in Excel 2007 so you specify the
  • start date comma the end date closed
  • paren and that throws out the saturdays
  • and sundays all right it's still a
  • problem though because it's counting
  • holiday so if we want to leave out the
  • holidays then we use equal net work days
  • from the start date to the end date
  • comma and then an optional argument
  • where the holiday so I'll choose that
  • I'll press f4 to lock that down and it
  • will calculate the number of work days
  • less holidays all right so now let's
  • calculate this for all of these will
  • copy this down and say fill without
  • formatting and you'll see that here
  • there are 351 days 251 days if you throw
  • out Saturdays and Sundays but 239 days
  • if you threw out all of these holidays
  • right great great function if you're in
  • the United States or actually if you're
  • not in any of the countries listed in
  • rows 2 through 6 all of these countries
  • your weekend is Friday and Saturday
  • nepal Saturday Afghanistan Thursday and
  • Friday here on Iran only friday and then
  • the completely evil one that nothing
  • will ever not nothing but it will be
  • difficult to deal with is Brunei I had
  • to go figure out where brunei is is it
  • true your weekend is friday and sunday
  • how miserable is that right what I don't
  • know it's a one day work week I guess I
  • don't know I don't know any way alright
  • so what if you have to calculate a
  • weekend that's not saturday and sunday
  • here's a manufacturing plant that's
  • working monday through saturday so
  • they're only work day off is sunday well
  • i think was excel 2010 they gave us
  • equal net work days dot int L hi aunty L
  • that starts out the same here's the
  • start date here's the end date and then
  • the new third argument we get to specify
  • what the weekend is and in this case it
  • will be sunday only it still isn't going
  • to handle brunette I where it's friday
  • and sunday but for any other two
  • consecutive days or one consecutive day
  • there's an option for that now and then
  • the holidays out here press at four and
  • you have your answer I'll do alt ESF or
  • paste special formulas enter and we can
  • copy that one down all right now however
  • died or or just anything that is a non
  • standard work week back in the day
  • barbershops you just be closed on
  • Sundays and Wednesdays I used to live up
  • in Ohio and we'd go shopping at the
  • hartville marketplace in flea market
  • this place wonderful place by the way if
  • you're there to see the pro football
  • hall of fame just 20 minutes up the road
  • they're open monday thursday friday and
  • saturday right so they're weekend then
  • is tuesday wednesday something how are
  • we ever going to do that with a network
  • day as well this is so cool they've
  • added this crazy new option that's not
  • documented in the tooltip so network
  • days I MTL here's the start date comma
  • here's the end date comma and then the
  • secret one that's not here in the
  • drop-down at all is in quotes seven
  • binary digits zeros or ones
  • starting with a Monday one means it's a
  • weekend 0 means is open so the Hartfield
  • marketplace they're open on mondays to
  • put a zero they're closed on tuesday and
  • wednesday they're open on thursday
  • friday saturday they're closed on sunday
  • closed the quotes holidays i don't know
  • there are no holidays at this place
  • because frankly if the fourth of july
  • falls on a monday or a thursday that's
  • their biggest day everyone is off from
  • working so they all go flying it at a
  • place be tough to get a parking spot
  • alright so the actual number of work
  • days between those two dates i'll right
  • click and fill without formatting
  • alright i love this one kind of secret
  • if you go to excel help you'll find it
  • but if you're just looking the tooltip
  • you will never know it's there unless of
  • course you own this book and on page 99
  • you read about it or if you saw this
  • video so either way cool click the eye
  • on the top right hand corner to buy the
  • book 10 bucks is an ebook 25 bucks for
  • the print book all of these amazing tips
  • two and a half months worth of podcast
  • on the palm of your hand alright date
  • math and Excel subtract the earlier date
  • from the late plus one that counts
  • Saturdays and Sundays to ignore weekends
  • use the network days function to not
  • count holidays you would use the third
  • argument at network days make sure to
  • press f4 for that for non-standard work
  • weeks network days dot international
  • that allows for any two or one
  • consecutive weekend and then there's a
  • secret 727 binary digit code for work
  • weeks that are not consecutive days even
  • in the country of Brunei you'd be able
  • to handle that Friday his sunday work
  • week why hain't want to take you for
  • stopping by we'll see you next time for
  • another nut cast from MrExcel

Download File

Download the sample file here: Podcast2023.xlsx

Title Photo: market-stand-vegetables-variety / pixabay