After episode 2363, a pair of great questions and a tip.
Tip 1: When specifying the Holiday Range in Excel, make the range into a table and then name the range. That way, when you add new holidays at the bottom, the named range will automatically expand.
Question 1: How many holidays fall between two dates?
Question 2: What is the list of holidays between two dates?
This video includes details on NETWORKDAYS.INTL and FILTER functions.
You will also see how I take two sub-formulas that were in different cells and combine them into a single formula by scooping the characters out of each formula.
Table of Contents
(0:00) Welcome
(0:17) Binary option for WEEKEND argument in Excel
(1:15) Table, Named Range for Holidays
(1:37) Named range extends after adding holidays
(1:56) How many days between dates
(2:06) Days excluding holidays
(2:54) Number of Holidays
(3:09) One formula
(3:45) List of holidays in Excel
(5:31) Recap
Tip 1: When specifying the Holiday Range in Excel, make the range into a table and then name the range. That way, when you add new holidays at the bottom, the named range will automatically expand.
Question 1: How many holidays fall between two dates?
Question 2: What is the list of holidays between two dates?
This video includes details on NETWORKDAYS.INTL and FILTER functions.
You will also see how I take two sub-formulas that were in different cells and combine them into a single formula by scooping the characters out of each formula.
Table of Contents
(0:00) Welcome
(0:17) Binary option for WEEKEND argument in Excel
(1:15) Table, Named Range for Holidays
(1:37) Named range extends after adding holidays
(1:56) How many days between dates
(2:06) Days excluding holidays
(2:54) Number of Holidays
(3:09) One formula
(3:45) List of holidays in Excel
(5:31) Recap
Transcript of the video:
How many holidays are between two dates, and what are those holidays?
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen. A question sent in after my video last week said, "How many days are holidays in a date range and which days are holidays in a date range?" Now I'm pretty excited about this.
In the previous video I used WORKDAY and NETWORKDAY, but there's the international versions of those that offer a weekend argument.
The international versions allow you to specify a weekend. This is the tool tip that you see.
And so, most people think that these 14 options are the only options, but there's another way to do this, the seven digit binary method, and the way it works is you pass a text string that seven characters long. This is called weekend.
So, zero means we're open.
One means it's a weekend, and it always starts on Monday. They're closed Tuesday, Thursday, Sunday.
Here, they're closed on Monday, open Tuesday, Wednesday, Thursday, Friday, Saturday, closed on Sunday.
Here, open on Monday, closed Tuesday, open on Wednesday, closed Thursday, open on Friday, closed Saturday, Sunday.
Also, shout out to Halston who saw my method for specifying the holiday range where I had a extra row here that we would insert new days before this.
Halston says, "You don't have to do that. Just make this into a table".
So, Control T for a table, make it into a table first, and then select the data portion of the table and give it a name like holiday range. That's the one I used last week.
No space in that, of course.
And then the advantage here is if we get a new holiday, so 1/1/24, holiday range automatically extends to include that new date.
Great feature. Thanks to Halston for sending that in.
Okay, so let's answer the question here of how many days are between these two dates, and I'm going to do this in a few steps.
First thing we want to do is just subtract the start date from the end date, and this excludes the start date.
So, we have to add one to that.
So, there's 365 days, including January 1st and including December 31st. Now here's the tricky part.
So, we're going to ask for equal NETWORKDAYS.international from the start date to the end date, and check out what I'm going to do here.
I don't want to exclude Saturdays and Sundays. I want to include every day.
My sneaky way of doing this is when I specify the weekend, Monday's not a weekend.
Neither's Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
No weekends at all, right?
And what that's going to do is that's going to count every single day except for the things in the holiday range.
Sorry, type holiday range like that.
So, there's 365 days total, 351 days without the holidays. And here, let's just test it.
So, from that date to this date, down here in the status bar of the count is 14.
Yes, it's working.
Of course, so number of days inclusive minus number of days without holidays, and we get 14.
Now, you might have to change that format to a number, and of course, we don't need any decimals.
Now to bring this all back together, I'm going to take that formula and put it in parenthesis here.
So, where we have G3, I'll type an open parenthesis, paste, close parenthesis, and then grab the H3 formula, press F2 to add it, Shift Home to select everything, Shift right arrow key to leave out the equal sign, Control C to copy, and then here where we have H3.
All right? So, there we go.
That's our formula to figure out how many holidays.
All right?
But then when I went back to the original question, it wasn't clear if they were looking for how many holidays or the list of holidays.
So, let's say that they were looking for the list of holidays.
This is a great new function, came along in September of 2019.
We want to filter the holiday range, and the include thing, we want to have two things.
I want to join them with an and.
So, the first thing I'm going to put in parenthesis, I want to say that the holiday range is greater than or equal to our start date, close parenthesis, and then a multiply.
So, the and operator in bullion logic is the multiply symbol, and we want to see if the holiday range is less than or equal to our end date.
Close that, that finishes off the include function.
In case you had something that had no holidays, I guess we should fill in the no, the if empty.
So, no holiday is in this range, and press enter. Okay, there we are.
That is the right answer with the wrong formatting.
That's very common with these dynamic arrays. They don't know how to format.
So, we change that to a short date and we get a list of holidays.
Let's just test it, make it go wider.
So, from 1/1/2022 to 12/31/24, get the whole list of holidays. I didn't format far enough.
Short date. All right, there you go.
Wow. All kinds of good stuff here.
My favorite one, when I say it's a favorite, I used these functions without knowing about the binary weekend argument for so long.
And then when I accidentally discovered it, how did I ever get to Excel Help, I don't know, and I saw that that was in there, I'm like, "Oh my gosh, this is so useful".
So, anytime I have a chance to tell people about this argument, I love to point that out.
Thanks to Halston for this great idea of making this into a table and then naming it after you make it into a table, so it automatically grows. A great feature there.
And then that formula, and that formula. Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen. A question sent in after my video last week said, "How many days are holidays in a date range and which days are holidays in a date range?" Now I'm pretty excited about this.
In the previous video I used WORKDAY and NETWORKDAY, but there's the international versions of those that offer a weekend argument.
The international versions allow you to specify a weekend. This is the tool tip that you see.
And so, most people think that these 14 options are the only options, but there's another way to do this, the seven digit binary method, and the way it works is you pass a text string that seven characters long. This is called weekend.
So, zero means we're open.
One means it's a weekend, and it always starts on Monday. They're closed Tuesday, Thursday, Sunday.
Here, they're closed on Monday, open Tuesday, Wednesday, Thursday, Friday, Saturday, closed on Sunday.
Here, open on Monday, closed Tuesday, open on Wednesday, closed Thursday, open on Friday, closed Saturday, Sunday.
Also, shout out to Halston who saw my method for specifying the holiday range where I had a extra row here that we would insert new days before this.
Halston says, "You don't have to do that. Just make this into a table".
So, Control T for a table, make it into a table first, and then select the data portion of the table and give it a name like holiday range. That's the one I used last week.
No space in that, of course.
And then the advantage here is if we get a new holiday, so 1/1/24, holiday range automatically extends to include that new date.
Great feature. Thanks to Halston for sending that in.
Okay, so let's answer the question here of how many days are between these two dates, and I'm going to do this in a few steps.
First thing we want to do is just subtract the start date from the end date, and this excludes the start date.
So, we have to add one to that.
So, there's 365 days, including January 1st and including December 31st. Now here's the tricky part.
So, we're going to ask for equal NETWORKDAYS.international from the start date to the end date, and check out what I'm going to do here.
I don't want to exclude Saturdays and Sundays. I want to include every day.
My sneaky way of doing this is when I specify the weekend, Monday's not a weekend.
Neither's Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
No weekends at all, right?
And what that's going to do is that's going to count every single day except for the things in the holiday range.
Sorry, type holiday range like that.
So, there's 365 days total, 351 days without the holidays. And here, let's just test it.
So, from that date to this date, down here in the status bar of the count is 14.
Yes, it's working.
Of course, so number of days inclusive minus number of days without holidays, and we get 14.
Now, you might have to change that format to a number, and of course, we don't need any decimals.
Now to bring this all back together, I'm going to take that formula and put it in parenthesis here.
So, where we have G3, I'll type an open parenthesis, paste, close parenthesis, and then grab the H3 formula, press F2 to add it, Shift Home to select everything, Shift right arrow key to leave out the equal sign, Control C to copy, and then here where we have H3.
All right? So, there we go.
That's our formula to figure out how many holidays.
All right?
But then when I went back to the original question, it wasn't clear if they were looking for how many holidays or the list of holidays.
So, let's say that they were looking for the list of holidays.
This is a great new function, came along in September of 2019.
We want to filter the holiday range, and the include thing, we want to have two things.
I want to join them with an and.
So, the first thing I'm going to put in parenthesis, I want to say that the holiday range is greater than or equal to our start date, close parenthesis, and then a multiply.
So, the and operator in bullion logic is the multiply symbol, and we want to see if the holiday range is less than or equal to our end date.
Close that, that finishes off the include function.
In case you had something that had no holidays, I guess we should fill in the no, the if empty.
So, no holiday is in this range, and press enter. Okay, there we are.
That is the right answer with the wrong formatting.
That's very common with these dynamic arrays. They don't know how to format.
So, we change that to a short date and we get a list of holidays.
Let's just test it, make it go wider.
So, from 1/1/2022 to 12/31/24, get the whole list of holidays. I didn't format far enough.
Short date. All right, there you go.
Wow. All kinds of good stuff here.
My favorite one, when I say it's a favorite, I used these functions without knowing about the binary weekend argument for so long.
And then when I accidentally discovered it, how did I ever get to Excel Help, I don't know, and I saw that that was in there, I'm like, "Oh my gosh, this is so useful".
So, anytime I have a chance to tell people about this argument, I love to point that out.
Thanks to Halston for this great idea of making this into a table and then naming it after you make it into a table, so it automatically grows. A great feature there.
And then that formula, and that formula. Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
