How can you figure out a date that is a certain number or workdays BEFORE a date? On the last episode of The Lab with Leo Laporte, I showed how to calculate a date 45 workdays after a certain date. Todays question is how to calculate the date 5 days before the end of the month. Episode 560 shows you how to calculate this value.
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:
Welcome back to the Mr. Excel netcast.
I'm Bill Jelen.
Today we have a question sitting by Vishnu from India.
If you have a question for the netcast, please feel free to either leave a voice mail or drop me an email and we'll get to you on a future podcast.
Now, it's completely coincidence that on Friday we were talking about how to find the end of the month.
And Vishnu's question happens to deal with finding the number of work days before the end of the month.
So, it's not just that he wants to find the last day of the month.
He wants to know, you know, what's three work days before that.
Now, he mentioned using the EOMONTH function.
And that's one of the functions that. we have to use the Analysis Toolpak.
So, make sure if you're in Excel 2003 or before you go to, Tools, Add-Ins and choose Analysis Toolpak.
Those of us on Excel 2007, it's automatically in you don't have to worry about turning on the add-in.
So, we asked for the EOMONTH function of that particular date and they want to know how many months out we want to go.
We don't want to go out any number of months, we just want to stay in the current month.
And so, we hit zero.
Now, one frustrating thing with EOMONTH and the WORKDAY function, that we're about to use is that it doesn't automatically format the cell as a date.
So, I'll select those cells use control+1, to get the Format Cells and choose a Date function.
I'm going to choose the one that shows me the day of the week.
So, we see that the end of the month here is Tuesday.
Now, the WORKDAY function is this beautiful function.
=WORKDAY(EOMONTH(B11,0) and then the number of work days out from that.
So, normally what I do my example is, you know, you start working than 45 days later you're off probation.
So, I put comma 45 there, but it turns out that it works perfectly with negative numbers.
So, if I want to know one day before the end of the month, we'll use the negative one and then I will copy that over, So, one day before the end of the month is July 30th.
Two work days before the end of the month is Friday.
Three work days before the end of the month is Thursday, July 26th.
So, using a combination of EOMONTH and the WORKDAY Function with a negative number, to figure out how many days before the end of the month.
Thanks to Vishnu for sending that question in.
Again, if you have a question for the podcast.
Please, feel free to send it in.
Thanks for stopping by.
See you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question sitting by Vishnu from India.
If you have a question for the netcast, please feel free to either leave a voice mail or drop me an email and we'll get to you on a future podcast.
Now, it's completely coincidence that on Friday we were talking about how to find the end of the month.
And Vishnu's question happens to deal with finding the number of work days before the end of the month.
So, it's not just that he wants to find the last day of the month.
He wants to know, you know, what's three work days before that.
Now, he mentioned using the EOMONTH function.
And that's one of the functions that. we have to use the Analysis Toolpak.
So, make sure if you're in Excel 2003 or before you go to, Tools, Add-Ins and choose Analysis Toolpak.
Those of us on Excel 2007, it's automatically in you don't have to worry about turning on the add-in.
So, we asked for the EOMONTH function of that particular date and they want to know how many months out we want to go.
We don't want to go out any number of months, we just want to stay in the current month.
And so, we hit zero.
Now, one frustrating thing with EOMONTH and the WORKDAY function, that we're about to use is that it doesn't automatically format the cell as a date.
So, I'll select those cells use control+1, to get the Format Cells and choose a Date function.
I'm going to choose the one that shows me the day of the week.
So, we see that the end of the month here is Tuesday.
Now, the WORKDAY function is this beautiful function.
=WORKDAY(EOMONTH(B11,0) and then the number of work days out from that.
So, normally what I do my example is, you know, you start working than 45 days later you're off probation.
So, I put comma 45 there, but it turns out that it works perfectly with negative numbers.
So, if I want to know one day before the end of the month, we'll use the negative one and then I will copy that over, So, one day before the end of the month is July 30th.
Two work days before the end of the month is Friday.
Three work days before the end of the month is Thursday, July 26th.
So, using a combination of EOMONTH and the WORKDAY Function with a negative number, to figure out how many days before the end of the month.
Thanks to Vishnu for sending that question in.
Again, if you have a question for the podcast.
Please, feel free to send it in.
Thanks for stopping by.
See you next time for another netcast from MrExcel.
