Using the EOMonth function to find the end of a month N months in the future. Episode 577 shows you how.
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're going to talk about finding the end of the month.
Now I know that we talked about this in episode 559 and Episode 540 and probably several episodes before that.
It's funny when you get into these long protracted arguments about the best way to do something, we kind of forget the original premise and the original premise was, the person was not allowed to use functions in the analysis Toolpak.
Now in excel 2007, the analysis toolpack has 89 functions that are part of the core excel but in earlier versions of excel if someone had not activated the add-in, they would get a name error instead of the actual function.
So in that original case the person said, hey my company doesn't allow us to use analysis Toolpak functions, because it's going to give the wrong results in some people's spreadsheets.
Well, If you don't have that problem then the simplest way to do is to find the end of the month is to use the EOMONTH function.
Check to see if you have any analysis Toolpak turned on with tools add-ins and then make sure that analysis Toolpak is checked.
It's always there.
But it's not checked by default.
Click OK and then you're able to use any of the 89 functions in the analysis Toolpak, including the end of month function EOMONTH.
Specify a date and then how many months in the future you want the end of the month for.
So here we're looking for the end of next month, so we would use a 1.
if we wanted the end of this month, We just use a 0 and hit enter.
Now unfortunately with many date functions may give us the right result, but the wrong format.
So we want to format cells the fast way to do that is Ctrl1 to format cells and we'll choose some sort of a date format.
Click OK and there you see the end of next month from January second is indeed February 28th.
This is much easier than having to worry about figure out if there's 28 or 29 days in February.
And so if you're allowed to use the analysis toolpak if there's no reason why you can't use the analysis Toolpak.
the EOMONTH function is much easier than what we discussed in episode 540 and 559 so thanks to Wayne for sending in that note.
if you ever see anything in a podcast where you say hey there's a better way to do that.
Drop me a note bill at MrExcel and we'll be able to get you on a future podcast.
Thanks thanks for stoping by. We'll see you next time for another netcast from MrExcel.
Today we're going to talk about finding the end of the month.
Now I know that we talked about this in episode 559 and Episode 540 and probably several episodes before that.
It's funny when you get into these long protracted arguments about the best way to do something, we kind of forget the original premise and the original premise was, the person was not allowed to use functions in the analysis Toolpak.
Now in excel 2007, the analysis toolpack has 89 functions that are part of the core excel but in earlier versions of excel if someone had not activated the add-in, they would get a name error instead of the actual function.
So in that original case the person said, hey my company doesn't allow us to use analysis Toolpak functions, because it's going to give the wrong results in some people's spreadsheets.
Well, If you don't have that problem then the simplest way to do is to find the end of the month is to use the EOMONTH function.
Check to see if you have any analysis Toolpak turned on with tools add-ins and then make sure that analysis Toolpak is checked.
It's always there.
But it's not checked by default.
Click OK and then you're able to use any of the 89 functions in the analysis Toolpak, including the end of month function EOMONTH.
Specify a date and then how many months in the future you want the end of the month for.
So here we're looking for the end of next month, so we would use a 1.
if we wanted the end of this month, We just use a 0 and hit enter.
Now unfortunately with many date functions may give us the right result, but the wrong format.
So we want to format cells the fast way to do that is Ctrl1 to format cells and we'll choose some sort of a date format.
Click OK and there you see the end of next month from January second is indeed February 28th.
This is much easier than having to worry about figure out if there's 28 or 29 days in February.
And so if you're allowed to use the analysis toolpak if there's no reason why you can't use the analysis Toolpak.
the EOMONTH function is much easier than what we discussed in episode 540 and 559 so thanks to Wayne for sending in that note.
if you ever see anything in a podcast where you say hey there's a better way to do that.
Drop me a note bill at MrExcel and we'll be able to get you on a future podcast.
Thanks thanks for stoping by. We'll see you next time for another netcast from MrExcel.
