How can you calculate work days between two dates when your business is closed on Sunday and Wednesday? WORKDAYS.INTL does not offer a Sunday and Wednesday option. Today, @ExcelWriter from Twitter checks in with a great solution to this question.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1779: Workdays Except Sunday And Wednesday.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen. Great question came in yesterday.
How do we calculate workdays when our workweek is we're closed on Sunday and Wednesday?
Alright.
So, just a little bit of history here.
Usually, yeah, if we just want to calculate the number of days, we do the subtraction, format that as a NUMER, 0 DECIMAL PLACES, and we'll see the number of days between the 2 dates.
So, here's today, here's the project due date, we know the number of days, but we're not working all of those days.
So, typically what we would do is use =NETWORKDAYS.
That assumes a workweek where you're closed on Saturday and Sunday, or now the new NETWORKDAYS INTERNATIONAL where we can specify a start date, a due date, and then what the weekend is.
Is it number 1, Saturday and Sunday, or some other unit, alright?
See, but there is no combination of Sunday and Wednesday only.
So, let's do just Sunday only for right now, , and then specify the holidays out here.
So, here's our list of holidays, we’ll press F4, and we're good to go, alright?
So, 103 days but 85 workdays, assuming that we are open every day except for Sunday, alright?
[ =NETWORKDAYS.INTL(B2,C2,11,$K$2:$K$10) ] So, the question Iist, well, we're also closed on Wednesdays.
How can we take Wednesdays out of there?
I started thinking about these huge array formulas and everything.
I want to send a shoutout to David Ringstrom.
I've been following David.
He’s @excelwriter on Twitter, has lots of great articles, and I tweeted about this yesterday.
He came back right away with this beautiful idea.
So, David's idea is to come over here and put in the first Wednesday, 8/21/13, and then the next Wednesday, 8/28/13, and we will grab that and just extend it down to include all the Wednesdays in the range, and then our holiday range will be this list of holidays.
F4.
ENTER.
72, and we'll copy it down.
[ =NETWORKDAYS.INTL(B2,C2,11,$K$2:$K$41) ] So, by making the list of Wednesdays in the holiday list, we're avoiding the Wednesdays.
This will be great for any kind of strange schedule, even if it was, you know, hey, we're closed every other Wednesday or the 4th Wednesday of every month.
You could add that to the holiday list and prevent it from coming through.
Great, great solution.
I want to thank David for sending that idea in.
Well, hey.
Alright.
I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1779: Workdays Except Sunday And Wednesday.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen. Great question came in yesterday.
How do we calculate workdays when our workweek is we're closed on Sunday and Wednesday?
Alright.
So, just a little bit of history here.
Usually, yeah, if we just want to calculate the number of days, we do the subtraction, format that as a NUMER, 0 DECIMAL PLACES, and we'll see the number of days between the 2 dates.
So, here's today, here's the project due date, we know the number of days, but we're not working all of those days.
So, typically what we would do is use =NETWORKDAYS.
That assumes a workweek where you're closed on Saturday and Sunday, or now the new NETWORKDAYS INTERNATIONAL where we can specify a start date, a due date, and then what the weekend is.
Is it number 1, Saturday and Sunday, or some other unit, alright?
See, but there is no combination of Sunday and Wednesday only.
So, let's do just Sunday only for right now, , and then specify the holidays out here.
So, here's our list of holidays, we’ll press F4, and we're good to go, alright?
So, 103 days but 85 workdays, assuming that we are open every day except for Sunday, alright?
[ =NETWORKDAYS.INTL(B2,C2,11,$K$2:$K$10) ] So, the question Iist, well, we're also closed on Wednesdays.
How can we take Wednesdays out of there?
I started thinking about these huge array formulas and everything.
I want to send a shoutout to David Ringstrom.
I've been following David.
He’s @excelwriter on Twitter, has lots of great articles, and I tweeted about this yesterday.
He came back right away with this beautiful idea.
So, David's idea is to come over here and put in the first Wednesday, 8/21/13, and then the next Wednesday, 8/28/13, and we will grab that and just extend it down to include all the Wednesdays in the range, and then our holiday range will be this list of holidays.
F4.
ENTER.
72, and we'll copy it down.
[ =NETWORKDAYS.INTL(B2,C2,11,$K$2:$K$41) ] So, by making the list of Wednesdays in the holiday list, we're avoiding the Wednesdays.
This will be great for any kind of strange schedule, even if it was, you know, hey, we're closed every other Wednesday or the 4th Wednesday of every month.
You could add that to the holiday list and prevent it from coming through.
Great, great solution.
I want to thank David for sending that idea in.
Well, hey.
Alright.
I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel.
