Ammar from Iran sends in a question about how to find the last Saturday of any given month. Episode 524 shows how to use a couple of analysis toolpak functions to solve this problem.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, we have a question sent in all the way from Iran.
This is from Amar.
If you have a question for the podcast, please feel free to leave us either an email or a voicemail and we'll get to you on a future podcast.
Amar says that he has a file of dates and he needs to find the last Saturday of the month, that falls into.
This is one of those problems, that I have to kind of work through the solution, bit by bit, by bit.
The first thing we want to do is use one of the functions that's in the analysis tool pack.
If you're in Excel 2007, you're good all the analysis tool pack functions automatically work for you.
But otherwise you might want to go to the tools, add-ins menu and make sure that the analysis tool pack is checked.
It's always installed by Excel, but not always turned on and the function we're going to use is a great function called EO month.
Stands for end the month.
We put in a date and then how many months after that date, we want to go.
Well in this case, we're just looking for the end of this month.
So, will enter that formula [ ctrl enter ] and copy the formula down.
Now, I've already formatted this column to show me the day of the week.
I did that with format cells and you can see that the last day of the month sometimes falls on Wednesday, Friday, Wednesday, Wednesday.
And basically, what we need to do now is figure out which day of the week that end of the month is.
So, there's another great function called weekday.
The weekday function specify our end of month, day.
And it gives us a number 1 through 7 and if we look you'll see that Sundays are 1, Mondays are 2 and so on through the week.
So, basically what we would have to do to get back to the last Saturday, is to take our end of month date from column B and subtract the weekday value, will copy that down.
You'll see that it works perfectly everywhere except for the months where the last day already was a Saturday.
So, let's see here all the way down here.
In this case, the last day of the month was Saturday March 31st.
Our formula currently is taking seven away from that.
So, we need to check to see if the weekday function is already a seven then we don't want it to subtract.
It will add another term here.
We'll add an IF function.
Well, say if the value in B1 is equal to 7 then we want to add 7 back, otherwise zero.
So, we end up with a formula B1 minus C,1 plus the if function.
If B1 is equal to 7 add 7 back, otherwise zero and we'll copy that down, you'll see that.
We get the exact answer that we need the last Saturday of the month.
Now, if you have another day that week for example, Wednesday or Thursday.
It's always a little bit tough you have to figure out the right combination of terms, what to do with the weekday.
Many times you're adding a few days or subtracting a few days depending on whether the last day the month falls before or after the day you're looking for.
Hey! There you have it.
A great question from Amar, all the way from Iran.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today, we have a question sent in all the way from Iran.
This is from Amar.
If you have a question for the podcast, please feel free to leave us either an email or a voicemail and we'll get to you on a future podcast.
Amar says that he has a file of dates and he needs to find the last Saturday of the month, that falls into.
This is one of those problems, that I have to kind of work through the solution, bit by bit, by bit.
The first thing we want to do is use one of the functions that's in the analysis tool pack.
If you're in Excel 2007, you're good all the analysis tool pack functions automatically work for you.
But otherwise you might want to go to the tools, add-ins menu and make sure that the analysis tool pack is checked.
It's always installed by Excel, but not always turned on and the function we're going to use is a great function called EO month.
Stands for end the month.
We put in a date and then how many months after that date, we want to go.
Well in this case, we're just looking for the end of this month.
So, will enter that formula [ ctrl enter ] and copy the formula down.
Now, I've already formatted this column to show me the day of the week.
I did that with format cells and you can see that the last day of the month sometimes falls on Wednesday, Friday, Wednesday, Wednesday.
And basically, what we need to do now is figure out which day of the week that end of the month is.
So, there's another great function called weekday.
The weekday function specify our end of month, day.
And it gives us a number 1 through 7 and if we look you'll see that Sundays are 1, Mondays are 2 and so on through the week.
So, basically what we would have to do to get back to the last Saturday, is to take our end of month date from column B and subtract the weekday value, will copy that down.
You'll see that it works perfectly everywhere except for the months where the last day already was a Saturday.
So, let's see here all the way down here.
In this case, the last day of the month was Saturday March 31st.
Our formula currently is taking seven away from that.
So, we need to check to see if the weekday function is already a seven then we don't want it to subtract.
It will add another term here.
We'll add an IF function.
Well, say if the value in B1 is equal to 7 then we want to add 7 back, otherwise zero.
So, we end up with a formula B1 minus C,1 plus the if function.
If B1 is equal to 7 add 7 back, otherwise zero and we'll copy that down, you'll see that.
We get the exact answer that we need the last Saturday of the month.
Now, if you have another day that week for example, Wednesday or Thursday.
It's always a little bit tough you have to figure out the right combination of terms, what to do with the weekday.
Many times you're adding a few days or subtracting a few days depending on whether the last day the month falls before or after the day you're looking for.
Hey! There you have it.
A great question from Amar, all the way from Iran.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
