September 5, 2011 was the 'Labor Day' Holiday in the States; Labor Day is celebrated in honor of those workers who have contributed economically and socially...but it can make spreadsheet calculations a little difficult [as can most holidays, weekends, vacation time]. Today, in Episode #1421, Bill shows us how to calculate the previous work day - particularly when that previous day is a holiday. Learn how to work with the =WORKDAY Function, Holidays, Weekends, a few Excel Shortcuts and More when calculating time with Excel.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1421.
Previous Work Day.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Just getting back from a long holiday weekend.
Yesterday, was labor day in the United States, no podcast yesterday.
No work yesterday, and it brings up a good question.
I want to show a calculation that figures out the previous work day.
So, I'm gonna put in today's date.
Now, I could use equal today, open paren close paren, but instead.
I'm just going to use [ ctrL ; (semicolon) ].
[ ctrl ; ], puts in today's date.
So, let's make sure we're change this here, to a long date and we say that's Tuesday, September 6.
Then I want to come up with a formula that's going to give me the previous work day.
Now, I'm assuming Monday through Friday, and I want to ignore the holiday, So, the way to do this is equal work day, starting from this Tuesday and I want to go the number of days is negative 1.
Okay! Now, if I normally did this, what they're going to do is they're going to assume that every Monday through Friday is a work day, and they're going to give me yesterday, There it can be Monday, but that's a holiday.
So, what we have to do here is we have an optional third argument, and I've entered a list of all the federal holidays for 2011 and 2012, you see that New Year's Eve was actually a holiday in 2011, go figure comma and then here the optional argument for holiday.
So, I will select that and we will now see that goes back to Friday.
So, that's very cool.
Now, a couple of tricks rather than have that list of holidays set out there.
You can select those and press [ F9 ] and it actually embeds the date code for each of those dates in the formula.
The range of this is five years, from now.
We're still going to be using these holidays from 2011-2012, unless someone remembers that still it's a good to go.
Now, what if you have a situation where you're not working, Monday through Friday.
Maybe you work Monday through Saturday or something like that.
Used to be it that.
That was very tough to deal with but now Now, they've given us a cool way to do that.
It's called equal work day international, and you see that there is now a weekend argument.
So, when I start from this day, go backwards minus one and then you can choose from this list.
So, Saturday only as the weekend is smart enough to or I guess Sunday only.
So, you work Monday through Saturday, Sunday only would be the argument and then you can do this calculation for pretty much any work week that you can come up with.
Hey! I want to thank your stopping by, we'll see you next time another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1421.
Previous Work Day.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Just getting back from a long holiday weekend.
Yesterday, was labor day in the United States, no podcast yesterday.
No work yesterday, and it brings up a good question.
I want to show a calculation that figures out the previous work day.
So, I'm gonna put in today's date.
Now, I could use equal today, open paren close paren, but instead.
I'm just going to use [ ctrL ; (semicolon) ].
[ ctrl ; ], puts in today's date.
So, let's make sure we're change this here, to a long date and we say that's Tuesday, September 6.
Then I want to come up with a formula that's going to give me the previous work day.
Now, I'm assuming Monday through Friday, and I want to ignore the holiday, So, the way to do this is equal work day, starting from this Tuesday and I want to go the number of days is negative 1.
Okay! Now, if I normally did this, what they're going to do is they're going to assume that every Monday through Friday is a work day, and they're going to give me yesterday, There it can be Monday, but that's a holiday.
So, what we have to do here is we have an optional third argument, and I've entered a list of all the federal holidays for 2011 and 2012, you see that New Year's Eve was actually a holiday in 2011, go figure comma and then here the optional argument for holiday.
So, I will select that and we will now see that goes back to Friday.
So, that's very cool.
Now, a couple of tricks rather than have that list of holidays set out there.
You can select those and press [ F9 ] and it actually embeds the date code for each of those dates in the formula.
The range of this is five years, from now.
We're still going to be using these holidays from 2011-2012, unless someone remembers that still it's a good to go.
Now, what if you have a situation where you're not working, Monday through Friday.
Maybe you work Monday through Saturday or something like that.
Used to be it that.
That was very tough to deal with but now Now, they've given us a cool way to do that.
It's called equal work day international, and you see that there is now a weekend argument.
So, when I start from this day, go backwards minus one and then you can choose from this list.
So, Saturday only as the weekend is smart enough to or I guess Sunday only.
So, you work Monday through Saturday, Sunday only would be the argument and then you can do this calculation for pretty much any work week that you can come up with.
Hey! I want to thank your stopping by, we'll see you next time another netcast from MrExcel.
