Calculate work days between two dates excluding weekends and holidays. This episode also handles 6-day workweeks, countries where the weekend is Friday and Saturday, plus businesses like barber shops or farm markets that have operating hours on Monday, Thursday, Friday, Saturday. Episode Recap:
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+ESF for Paste Special Formulas
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+ESF for Paste Special Formulas
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2023 - Calculate Workdays, Even for Non-Standard Workweeks!
I'll be podcasting this entire book, click the “i” 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 Fill Week Days. You right-click the fill handle, drag and when you let go, you choose Fill Weekdays, and you see that it fills Monday-Friday. Works great for a lot of countries in the world, but there's countries in the world where the weekend is not Saturday-Sunday, right? And just sorry, Microsoft Excel doesn't care about you, alright! So, we're going to talk about how to calculate the number of workdays 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 +1, and you'll see how many work days there are, right, that works great if you work every day, right?
But if you want to leave out the weekends, the Saturdays and Sundays, =NETWORKDAYS has been around while in the Analysis tool 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, ), and that throws out the Saturdays and Sundays alright, it's still a problem, though, because it's counting holidays. If we want to leave out the holidays, then we use =NETWORKDAYS from the start date to the end date, comma, and then an optional argument where the holiday is. So I'll choose that, I'll press F4 to lock that down, and it will calculate the number of workdays less holidays, alright. 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-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 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? I don't know, it's a one day work week, I guess, I don't know, anyway, 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 their only work day off is Sunday, well, I think it was Excel 2010 they gave us =NETWORKDAYS.INTL! 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 Brunei 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 F4, and you have your answer. I'll do Alt E S F or Paste Special Formulas, Enter, and we can copy that one down, alright. Now, how a leopard died(?), or just anything that is a non-standard work week. Back in the day, barbershops used to be closed on Sundays and Wednesdays. I used to live up in Ohio, and we'd go shopping at the Hartville marketplace, the 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 their weekend then is Tuesday, Wednesday, something, how are we ever going to do that with a NETWORKDAYS? Well, this is so cool, they've added this crazy new option that’s not documented in the tooltip. So NETWORKDAYS.INTL, here's the start date, comma, here's the end date, comma, and then the secret one that's not here in the dropdown at all is, in quotes, 7 binary digits! 0’s and 1’s, starting with a Monday, 1 means it's a weekend, 0 means it’s open. So the Hartville marketplace, they're open on Mondays, so put a 0, they're closed on Tuesday and Wednesday, they're open on Thursday, Friday, Saturday, they're closed on Sunday, close the quotes. Holidays, I don't know, there are no holidays at this place because, frankly, if the 4th of July falls on a Monday or a Thursday, THAT's their biggest day, everyone is off from working, so they all go flying in that place, it’ll be tough to get a parking spot. Alright so, the actual number of workdays 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 “i” on the top-right hand corner to buy the book, $10 is an e-book, $25 for the print book, all of these amazing tips, 2.5 months’ worth of podcast, all on the palm of your hand. Alright, date math in Excel, subtract the earlier date from the late +1, that counts out Saturdays and Sundays.
To ignore weekend’s use the NETWORKDAYS function to not count holidays, you would use the third argument at NETWORKDAYS, make sure to press F4 for that. For non-standard workweeks, NETWORKDAYS.INTL, that allows for any 2 or 1 consecutive weekend. And then there's a secret 7-binary digit code for workweeks that are not consecutive days, even in the country of Brunei, you'd be able to handle that Friday and Sunday workweek.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
I'll be podcasting this entire book, click the “i” 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 Fill Week Days. You right-click the fill handle, drag and when you let go, you choose Fill Weekdays, and you see that it fills Monday-Friday. Works great for a lot of countries in the world, but there's countries in the world where the weekend is not Saturday-Sunday, right? And just sorry, Microsoft Excel doesn't care about you, alright! So, we're going to talk about how to calculate the number of workdays 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 +1, and you'll see how many work days there are, right, that works great if you work every day, right?
But if you want to leave out the weekends, the Saturdays and Sundays, =NETWORKDAYS has been around while in the Analysis tool 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, ), and that throws out the Saturdays and Sundays alright, it's still a problem, though, because it's counting holidays. If we want to leave out the holidays, then we use =NETWORKDAYS from the start date to the end date, comma, and then an optional argument where the holiday is. So I'll choose that, I'll press F4 to lock that down, and it will calculate the number of workdays less holidays, alright. 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-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 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? I don't know, it's a one day work week, I guess, I don't know, anyway, 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 their only work day off is Sunday, well, I think it was Excel 2010 they gave us =NETWORKDAYS.INTL! 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 Brunei 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 F4, and you have your answer. I'll do Alt E S F or Paste Special Formulas, Enter, and we can copy that one down, alright. Now, how a leopard died(?), or just anything that is a non-standard work week. Back in the day, barbershops used to be closed on Sundays and Wednesdays. I used to live up in Ohio, and we'd go shopping at the Hartville marketplace, the 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 their weekend then is Tuesday, Wednesday, something, how are we ever going to do that with a NETWORKDAYS? Well, this is so cool, they've added this crazy new option that’s not documented in the tooltip. So NETWORKDAYS.INTL, here's the start date, comma, here's the end date, comma, and then the secret one that's not here in the dropdown at all is, in quotes, 7 binary digits! 0’s and 1’s, starting with a Monday, 1 means it's a weekend, 0 means it’s open. So the Hartville marketplace, they're open on Mondays, so put a 0, they're closed on Tuesday and Wednesday, they're open on Thursday, Friday, Saturday, they're closed on Sunday, close the quotes. Holidays, I don't know, there are no holidays at this place because, frankly, if the 4th of July falls on a Monday or a Thursday, THAT's their biggest day, everyone is off from working, so they all go flying in that place, it’ll be tough to get a parking spot. Alright so, the actual number of workdays 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 “i” on the top-right hand corner to buy the book, $10 is an e-book, $25 for the print book, all of these amazing tips, 2.5 months’ worth of podcast, all on the palm of your hand. Alright, date math in Excel, subtract the earlier date from the late +1, that counts out Saturdays and Sundays.
To ignore weekend’s use the NETWORKDAYS function to not count holidays, you would use the third argument at NETWORKDAYS, make sure to press F4 for that. For non-standard workweeks, NETWORKDAYS.INTL, that allows for any 2 or 1 consecutive weekend. And then there's a secret 7-binary digit code for workweeks that are not consecutive days, even in the country of Brunei, you'd be able to handle that Friday and Sunday workweek.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
