Calculate the last day of this month, using a new trick sent in by Bob. Episode 688 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today I have a note sent in by Bob and Bob comes up with a better trick for one of my favorite tricks.
This talk is about how to find the last day of a month and typically I do this by using the DATE function. It once a year a month and a day.
All right, well you know to figure out the last date of the month, hard to do 30 days 31 days in February's it's 28 or is it a leap year with 29.
So, my standard trick for this is to use the year of the date and then 1 plus the month of the date.
So, what I'm trying to do here is if we're in February, I jump ahead to the first of the next month.
So, I don't bother trying to figure out how many days this month has.
I just go to the first of the next month which gives me March 1st and then I subtract one from that and we get back to February 29th.
You know it's it's one of those geeky solutions, that's been floating around forever.
Well, Bob said hey, you know this date function is so robust it can understand what you mean if you ask for the 0th day.
If I asked for the 0th day of next month, it automatically gives me the last day of this month.
Extremely cool.
Now, in the old Excel of course if you have the analysis toolpak turned on, or in the new Excel you have the ability to use the EOMONTH.
=EOMONTH(, from this date and how many months out.
Now, no months out just zero and that will give you the right answer in the wrong format.
So, we Format Cells choose a Date.
So, if you use the EOMONTH, you don't have to worry about this, but if you've been using this trick, of finding the first of the next month and subtracting one from a Bob's trick of just asking for the 0th day of the next month.
Works great.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today I have a note sent in by Bob and Bob comes up with a better trick for one of my favorite tricks.
This talk is about how to find the last day of a month and typically I do this by using the DATE function. It once a year a month and a day.
All right, well you know to figure out the last date of the month, hard to do 30 days 31 days in February's it's 28 or is it a leap year with 29.
So, my standard trick for this is to use the year of the date and then 1 plus the month of the date.
So, what I'm trying to do here is if we're in February, I jump ahead to the first of the next month.
So, I don't bother trying to figure out how many days this month has.
I just go to the first of the next month which gives me March 1st and then I subtract one from that and we get back to February 29th.
You know it's it's one of those geeky solutions, that's been floating around forever.
Well, Bob said hey, you know this date function is so robust it can understand what you mean if you ask for the 0th day.
If I asked for the 0th day of next month, it automatically gives me the last day of this month.
Extremely cool.
Now, in the old Excel of course if you have the analysis toolpak turned on, or in the new Excel you have the ability to use the EOMONTH.
=EOMONTH(, from this date and how many months out.
Now, no months out just zero and that will give you the right answer in the wrong format.
So, we Format Cells choose a Date.
So, if you use the EOMONTH, you don't have to worry about this, but if you've been using this trick, of finding the first of the next month and subtracting one from a Bob's trick of just asking for the 0th day of the next month.
Works great.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
