This video is all about the Excel function for WORKDAY.INTL. It allows you to make sure that you are always scheduling an event to occur on a Saturday, or Tues/Thurs or Friday.
The secret here is the alternate syntax for the Weekend argument.
Table of Contents
(0:00) Find a particular Saturday in Excel
(0:25) WORKDAY introduction
(1:37) Find a particular Saturday in Excel
(2:32) Find the next Tuesday
(5:02) Find next Tuesday or Thursday
(5:46) Handling holidays
(6:58) Wrap-up
The secret here is the alternate syntax for the Weekend argument.
Table of Contents
(0:00) Find a particular Saturday in Excel
(0:25) WORKDAY introduction
(1:37) Find a particular Saturday in Excel
(2:32) Find the next Tuesday
(5:02) Find next Tuesday or Thursday
(5:46) Handling holidays
(6:58) Wrap-up
Transcript of the video:
We need a formula that is going to generate a particular Saturday from today.
Today's question from Robert. Scheduling haircuts for a correctional facility.
You start with an intake date. That's the day the person arrives.
There's a Phase Up on the following Tuesday.
The first haircut is the Friday after that Tuesday. And then monthly haircuts four weeks after that.
But those monthly haircuts always have to be on Saturday.
There's a beautiful tool in Excel to solve this, but it is not obvious.
And the feature that we want is not even documented in the ToolTip.
The function I'm talking about is WORKDAY.INTL.
You give it a starting date and you want to know a certain number of workdays from that starting date.
And then you get to define the weekend.
Now, if you're just using the ToolTip, you think that the only possible weekends are Saturday Sunday, Sunday Monday, Monday Tuesday, or just single days, Thursday, Friday, Sunday.
But there's another way to specify the weekend and that's to give it a text string with seven digits, seven binary digits.
And those seven digits stand for Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
And you put a 1 if it's a weekend. So if we're not working...
Think about the prison barber here is only working on Saturday.
So for that guy or girl, Monday is a weekend, Tuesday is a weekend, Wednesday is a weekend, Thursday's a weekend, Friday's a weekend, Saturday is a workday, Sunday is a weekend.
So just starting here from today, =TODAY(). How many Saturdays out from today?
I'll just choose one of these at random here, so =WORKDAY.INTL.
Why is it “international”?
Originally, the WORKDAY function always assumed the weekend was Saturday Sunday.
And somewhere back, I think Excel 2007, they gave us the International version to handle countries where the weekend is two other days.
But thankfully, they gave us this argument here, which allows us to handle any kind of a weekend situation, including every day except for Saturday is a weekend for this barber.
So we start from this date, April 26th. How many work days out do we want to go?
We want to go four work days out. And then what's the weekend?
I type in quotes.
And as I type this, I'm saying, "This is Monday and that's a weekend, Tuesday, Wednesday, Thursday, Friday, Saturday is the workday, Sunday is the weekend".
All right, so let's see how we would actually solve this. So you put in the intake date here.
What's the next Tuesday?
So if the intake person only happens on Tuesday, let's define their weekend. The first digit is Monday.
They don't work on Monday, or they do something else on Monday, so that's a weekend, yes.
Tuesday's a workday.
Wednesday, Thursday, Friday, Saturday, Sunday are weekends for the intake person. So equal WORKDAY.INTL from the start date.
How many days?
We want the next available Tuesday, and the weekend definition is this.
Okay, now watch out for this.
This function was originally part of the Analysis ToolPak originally written in VBA.
Those functions aren't smart enough to change to a date, so this is a very common. That's a date serial number there.
You have to come up here and change to either short date or long date.
All right, so from today, April 26th, the next Tuesday is May 3rd.
Now this is funny.
Today actually is a Tuesday, and it's choosing the next one.
It'll be interesting to find out if that's actually going to work, okay, and then the next Friday after that.
At this point, it'd be very tempting just to put in plus three, I get that, but I suspect the actual schedule's a little bit more complicated, which we'll get to on the next sheet.
So, Friday's the only days that the first haircut happens.
So Monday weekend, Tuesday, Wednesday, Thursday, Friday is available.
Saturday, Sunday are weekends.
Just copy this formula down, and that will get me the Friday after that Tuesday, all right.
And then we want to go on a four week schedule, and here we're back to Saturday.
So Monday is a weekend, Tuesday, Wednesday, Thursday, Friday, Saturday's available, Sunday's a weekend.
And this time we want to go out five Saturdays. So you get your haircut on Friday.
The first Saturday is the next day, right? I'm not counting that one.
We have to count that one, but then I want basically four weeks after that.
So now here, we're going to do WORKDAY.INTL from that Friday.
Number of days out is five, and then the weekend is that cell.
All right, and that gets us to Saturday, June 4th. Let's do little test here.
That should be about 29 days later, 29, perfect.
Okay, now let's make this a little bit more real or complicated.
When people send questions in, they try and simplify it. "Hey, I'm just trying to figure out that Saturday.
I've got everything else figured out".
What if the Phase Up is actually the Tuesday or Thursday after intake and then haircut is Friday after that and then the fifth Saturday after that, which makes this a little bit more difficult because we don't know if it's coming from a Tuesday or a Thursday.
So the change here is, remember this is Monday, so Tuesday's available, Wednesday's a weekend, Thursday's available, Friday, Saturday, Sunday are weekends.
And then the next Friday after that just uses the same Friday definition over there.
And then the fifth Saturday after that works out fine.
The last element of this function that might be useful is this fourth argument out here.
It's optional, and we haven't used it to this point, but it's where you could define the holidays.
So maybe the barber doesn't work on the 4th of July, which is Independence Day in the United States, Christmas, or New Years.
So I just put a whole bunch of years of those three dates each year out here as a list of holidays.
And then I changed all of the work date on International to have an optional fourth argument to say, "Hey, if it falls on age three to age 14, any day in there, don't count that one".
It has to skip over to the next one.
And I was curious about what would happen here on the subsequent haircuts.
So from June 6th, four Saturdays out, would've been July 4th, but it bumps them out to July 11th.
And even, let's adjust this here, even if your haircut didn't fall on July 4th, it's still going to move you out one extra week because all the people who are going to get their haircut on July 4th moved to July 11th.
That means the July 11th people moved to July 18th, so 35 days between in that particular case.
I love how sometimes we have a problem in Excel and the solution is not at all obvious.
And that's the WORKDAY.INTL dating back to Excel 2007 is perfect at generating an answer that's always going to fall on a Tuesday or Thursday, always fall on a Friday, always fall on a Saturday.
Hey, check out my new book, MrExcel 2022 Boosting Excel.
Click the i in the top right hand corner for more information about that.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It 's a super fast way to learn.
Today's question from Robert. Scheduling haircuts for a correctional facility.
You start with an intake date. That's the day the person arrives.
There's a Phase Up on the following Tuesday.
The first haircut is the Friday after that Tuesday. And then monthly haircuts four weeks after that.
But those monthly haircuts always have to be on Saturday.
There's a beautiful tool in Excel to solve this, but it is not obvious.
And the feature that we want is not even documented in the ToolTip.
The function I'm talking about is WORKDAY.INTL.
You give it a starting date and you want to know a certain number of workdays from that starting date.
And then you get to define the weekend.
Now, if you're just using the ToolTip, you think that the only possible weekends are Saturday Sunday, Sunday Monday, Monday Tuesday, or just single days, Thursday, Friday, Sunday.
But there's another way to specify the weekend and that's to give it a text string with seven digits, seven binary digits.
And those seven digits stand for Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
And you put a 1 if it's a weekend. So if we're not working...
Think about the prison barber here is only working on Saturday.
So for that guy or girl, Monday is a weekend, Tuesday is a weekend, Wednesday is a weekend, Thursday's a weekend, Friday's a weekend, Saturday is a workday, Sunday is a weekend.
So just starting here from today, =TODAY(). How many Saturdays out from today?
I'll just choose one of these at random here, so =WORKDAY.INTL.
Why is it “international”?
Originally, the WORKDAY function always assumed the weekend was Saturday Sunday.
And somewhere back, I think Excel 2007, they gave us the International version to handle countries where the weekend is two other days.
But thankfully, they gave us this argument here, which allows us to handle any kind of a weekend situation, including every day except for Saturday is a weekend for this barber.
So we start from this date, April 26th. How many work days out do we want to go?
We want to go four work days out. And then what's the weekend?
I type in quotes.
And as I type this, I'm saying, "This is Monday and that's a weekend, Tuesday, Wednesday, Thursday, Friday, Saturday is the workday, Sunday is the weekend".
All right, so let's see how we would actually solve this. So you put in the intake date here.
What's the next Tuesday?
So if the intake person only happens on Tuesday, let's define their weekend. The first digit is Monday.
They don't work on Monday, or they do something else on Monday, so that's a weekend, yes.
Tuesday's a workday.
Wednesday, Thursday, Friday, Saturday, Sunday are weekends for the intake person. So equal WORKDAY.INTL from the start date.
How many days?
We want the next available Tuesday, and the weekend definition is this.
Okay, now watch out for this.
This function was originally part of the Analysis ToolPak originally written in VBA.
Those functions aren't smart enough to change to a date, so this is a very common. That's a date serial number there.
You have to come up here and change to either short date or long date.
All right, so from today, April 26th, the next Tuesday is May 3rd.
Now this is funny.
Today actually is a Tuesday, and it's choosing the next one.
It'll be interesting to find out if that's actually going to work, okay, and then the next Friday after that.
At this point, it'd be very tempting just to put in plus three, I get that, but I suspect the actual schedule's a little bit more complicated, which we'll get to on the next sheet.
So, Friday's the only days that the first haircut happens.
So Monday weekend, Tuesday, Wednesday, Thursday, Friday is available.
Saturday, Sunday are weekends.
Just copy this formula down, and that will get me the Friday after that Tuesday, all right.
And then we want to go on a four week schedule, and here we're back to Saturday.
So Monday is a weekend, Tuesday, Wednesday, Thursday, Friday, Saturday's available, Sunday's a weekend.
And this time we want to go out five Saturdays. So you get your haircut on Friday.
The first Saturday is the next day, right? I'm not counting that one.
We have to count that one, but then I want basically four weeks after that.
So now here, we're going to do WORKDAY.INTL from that Friday.
Number of days out is five, and then the weekend is that cell.
All right, and that gets us to Saturday, June 4th. Let's do little test here.
That should be about 29 days later, 29, perfect.
Okay, now let's make this a little bit more real or complicated.
When people send questions in, they try and simplify it. "Hey, I'm just trying to figure out that Saturday.
I've got everything else figured out".
What if the Phase Up is actually the Tuesday or Thursday after intake and then haircut is Friday after that and then the fifth Saturday after that, which makes this a little bit more difficult because we don't know if it's coming from a Tuesday or a Thursday.
So the change here is, remember this is Monday, so Tuesday's available, Wednesday's a weekend, Thursday's available, Friday, Saturday, Sunday are weekends.
And then the next Friday after that just uses the same Friday definition over there.
And then the fifth Saturday after that works out fine.
The last element of this function that might be useful is this fourth argument out here.
It's optional, and we haven't used it to this point, but it's where you could define the holidays.
So maybe the barber doesn't work on the 4th of July, which is Independence Day in the United States, Christmas, or New Years.
So I just put a whole bunch of years of those three dates each year out here as a list of holidays.
And then I changed all of the work date on International to have an optional fourth argument to say, "Hey, if it falls on age three to age 14, any day in there, don't count that one".
It has to skip over to the next one.
And I was curious about what would happen here on the subsequent haircuts.
So from June 6th, four Saturdays out, would've been July 4th, but it bumps them out to July 11th.
And even, let's adjust this here, even if your haircut didn't fall on July 4th, it's still going to move you out one extra week because all the people who are going to get their haircut on July 4th moved to July 11th.
That means the July 11th people moved to July 18th, so 35 days between in that particular case.
I love how sometimes we have a problem in Excel and the solution is not at all obvious.
And that's the WORKDAY.INTL dating back to Excel 2007 is perfect at generating an answer that's always going to fall on a Tuesday or Thursday, always fall on a Friday, always fall on a Saturday.
Hey, check out my new book, MrExcel 2022 Boosting Excel.
Click the i in the top right hand corner for more information about that.
If you like these videos, please, down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the comments below.
Now if you love Excel, check out my new courses on the Retrieve platform.
They are video courses but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It 's a super fast way to learn.
