Vicki: I have a list of dates and I'm looking for a formula to calculate the previous workday only if the date in the list is a weekend or holiday.
Why: Accrue Fed Fund Interest Daily (Fed Funds Rate only published on Business Days).
This is mildly complex, because you need to be able to create a named range for the holidays, and after building the long formula, Excel will likely give you the date serial number instead of the formatted date.
Table of Contents
(0:00) Introduction
(0:23) Create a named range with holidays
(1:23) Excel formula to detect if date is a workday using NETWORKDAY function
(2:11) WORKDAY function to go back one work day
(3:15) Formatting serial numbers as dates
Why: Accrue Fed Fund Interest Daily (Fed Funds Rate only published on Business Days).
This is mildly complex, because you need to be able to create a named range for the holidays, and after building the long formula, Excel will likely give you the date serial number instead of the formatted date.
Table of Contents
(0:00) Introduction
(0:23) Create a named range with holidays
(1:23) Excel formula to detect if date is a workday using NETWORKDAY function
(2:11) WORKDAY function to go back one work day
(3:15) Formatting serial numbers as dates
Transcript of the video:
When we're looking up at the Fed funds rates, use the previous workday, if not a workday.
Great question from Vicky today.
She has a list of dates, and I'm looking for a formula to calculate the previous workday only if the date in the list is a weekend or a holiday.
She's trying to accrue the Fed fund interest daily, and those rates are only published on business days. So, she has to go back a day.
All right. So, the first question here is all these dates.
Is it a weekend or a holiday? All right.
But before we can do that, we have to take this extra step somewhere in this workbook at a named range with the Federal holidays. So, I just have a worksheet here called holidays.
These are the federal holidays plus I added Groundhogs Day in just so it would fall in the range of my dates. And check this out at the bottom.
A date way in the future with a note here to insert new rows above this row.
So, to create a named range, you're going to select all of these holidays and choose something to call it. You can call it holidays.
I'm going to call it holiday range. You have to click here in the name box.
That's to the left of the formula bar. Now, no spaces in this.
So, holiday range like that. So, no spaces.
I still haven't created the name yet.
I have to press enter in order to create that name.
The test to make sure that you did that correctly, just click out here, and then open the name box and choose holiday range and make sure that it selected your range. Question, is this day a weekend or a holiday?
We're going to use a great function called network days.
The thing that's awesome about network days is it includes the first day as a day.
Normally, if you would just do end date minus start date, you're not going to count both the end date and the start date. But here you do.
So, we're starting from A2, and we're going to A2. Isn't that crazy?
And then, here for the holiday argument, we're going to specify that range we just created called holiday range.
And what will happen there is if it's a regular day, not a holiday, not a weekend, you're going to get a one.
But anytime it's a Saturday or a Sunday, you're going to get a zero there.
And then, if it's a holiday, remember my joke about including Groundhogs Day. So, that shows up as a zero.
So, now we have an easy way to detect if any given day is a workday or not.
Here's that formula.
We're going to say equal if, open parentheses, network days is equal to one, then comma, we want A2.
Otherwise, so another comma. All right.
Now, here's the hard part. We have to go back not just one day.
Here on Sunday, we have to go back two days to Friday.
And if it was a holiday Monday, we'd have to go back three days to the previous Friday.
So, that's going to be a function called Workday. The Workday function.
And we start from A2, and then the number of days to go, most people don't know this.
This can be negative. So, we want to go back to the previous Workday.
Negative one, workdays.
And oh, by the way, include the same range of holiday range, like that. So, we close the workday function.
We're not done, though, because that last parenthesis is red.
That means that somewhere we're unbalanced.
Remember we started here with an if function and that parenthesis is black, so we need one more parenthesis there in order for it to be balanced.
You see that the last parenthesis is black.
Now, I'm going to press control enter here to enter that in all of the values. Here's the gotcha.
Workday and Network Day, those functions are from the analysis tool pack.
Became part of Excel as part of Excel 2007.
And for whatever reason, those are not programmed to automatically convert to dates. So, you get these weird serial numbers.
Choose that range, come up here on the home tab, and change to either a short date or a long date.
It doesn't matter which one you want. Now, let's just do a test here.
So, like here, Friday, Saturday, and Sunday, all backdate to the rate from Friday.
My fake holiday here of Groundhog's Day, it backdates to Wednesday.
These Saturdays and Sundays backdate back to Friday.
So, it is working. All right?
There is the formula you want to use.
But before you can do that, you have to create the holiday range with the named range.
And you have to be prepared that once you enter this formula, you're going to get serial numbers instead of dates, and you're going to have to reformat those as dates.
Once all that's done, though, it's going to be working and working great.
Thanks to Vicki for sending that question in, and thanks to you for stopping by.
We'll see you next time for another net cast from MrExcel.
Great question from Vicky today.
She has a list of dates, and I'm looking for a formula to calculate the previous workday only if the date in the list is a weekend or a holiday.
She's trying to accrue the Fed fund interest daily, and those rates are only published on business days. So, she has to go back a day.
All right. So, the first question here is all these dates.
Is it a weekend or a holiday? All right.
But before we can do that, we have to take this extra step somewhere in this workbook at a named range with the Federal holidays. So, I just have a worksheet here called holidays.
These are the federal holidays plus I added Groundhogs Day in just so it would fall in the range of my dates. And check this out at the bottom.
A date way in the future with a note here to insert new rows above this row.
So, to create a named range, you're going to select all of these holidays and choose something to call it. You can call it holidays.
I'm going to call it holiday range. You have to click here in the name box.
That's to the left of the formula bar. Now, no spaces in this.
So, holiday range like that. So, no spaces.
I still haven't created the name yet.
I have to press enter in order to create that name.
The test to make sure that you did that correctly, just click out here, and then open the name box and choose holiday range and make sure that it selected your range. Question, is this day a weekend or a holiday?
We're going to use a great function called network days.
The thing that's awesome about network days is it includes the first day as a day.
Normally, if you would just do end date minus start date, you're not going to count both the end date and the start date. But here you do.
So, we're starting from A2, and we're going to A2. Isn't that crazy?
And then, here for the holiday argument, we're going to specify that range we just created called holiday range.
And what will happen there is if it's a regular day, not a holiday, not a weekend, you're going to get a one.
But anytime it's a Saturday or a Sunday, you're going to get a zero there.
And then, if it's a holiday, remember my joke about including Groundhogs Day. So, that shows up as a zero.
So, now we have an easy way to detect if any given day is a workday or not.
Here's that formula.
We're going to say equal if, open parentheses, network days is equal to one, then comma, we want A2.
Otherwise, so another comma. All right.
Now, here's the hard part. We have to go back not just one day.
Here on Sunday, we have to go back two days to Friday.
And if it was a holiday Monday, we'd have to go back three days to the previous Friday.
So, that's going to be a function called Workday. The Workday function.
And we start from A2, and then the number of days to go, most people don't know this.
This can be negative. So, we want to go back to the previous Workday.
Negative one, workdays.
And oh, by the way, include the same range of holiday range, like that. So, we close the workday function.
We're not done, though, because that last parenthesis is red.
That means that somewhere we're unbalanced.
Remember we started here with an if function and that parenthesis is black, so we need one more parenthesis there in order for it to be balanced.
You see that the last parenthesis is black.
Now, I'm going to press control enter here to enter that in all of the values. Here's the gotcha.
Workday and Network Day, those functions are from the analysis tool pack.
Became part of Excel as part of Excel 2007.
And for whatever reason, those are not programmed to automatically convert to dates. So, you get these weird serial numbers.
Choose that range, come up here on the home tab, and change to either a short date or a long date.
It doesn't matter which one you want. Now, let's just do a test here.
So, like here, Friday, Saturday, and Sunday, all backdate to the rate from Friday.
My fake holiday here of Groundhog's Day, it backdates to Wednesday.
These Saturdays and Sundays backdate back to Friday.
So, it is working. All right?
There is the formula you want to use.
But before you can do that, you have to create the holiday range with the named range.
And you have to be prepared that once you enter this formula, you're going to get serial numbers instead of dates, and you're going to have to reformat those as dates.
Once all that's done, though, it's going to be working and working great.
Thanks to Vicki for sending that question in, and thanks to you for stopping by.
We'll see you next time for another net cast from MrExcel.
