Look Who it Was Random video

Excel - Calculate Month Ending Date from Date in Excel - New EOMONTH function - Episode 688

861 views · Published 19 February 2009 · 2:19 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Calculate Month Ending Date from Date in Excel - New EOMONTH function.

Welcome back to the MrExcel netcast! In today's episode, we have a special trick sent in by Bob that will make finding the last day of the month even easier. As you may know, I have a favorite trick for this using the DATE function, but Bob's method takes it to the next level.

Typically, to find the last day of the month, I would use the year and month of the date and add 1 to the month. This would give me the first day of the next month, and then I would subtract 1 to get the last day of the current month. However, Bob's trick involves using the 0th day of the next month in the DATE function, which automatically gives us the last day of the current month. How cool is that?

Of course, for those of you using the analysis toolpak or the newer versions of Excel, you can also use the EOMONTH function to get the last day of the month. Simply enter the date and 0 for the number of months, and you'll get the correct answer. Just remember to format the cell as a date.

So, whether you prefer the traditional method or Bob's clever trick, finding the last day of the month will no longer be a hassle. Thanks for tuning in to this netcast from MrExcel. Don't forget to subscribe and hit the notification bell to stay updated on our latest Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Calculate the last day of this month, using a new trick sent in by Bob. Episode 688 shows you how.

Table of Contents:
(00:00) Introduction 
(00:14) Bob's Improved Trick 
(00:24) Finding the Last Day of a Month 
(00:39) Standard Trick for Finding Last Date 
(00:56) Bob's Solution 
(01:08) Using the 0th Day 
(01:23) EOMONTH Function 
(01:37) Formatting the Date 
(01:47) Comparison of Tricks 
(01:58) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
DATE function in Excel
EOMONTH function in Excel
Excel analysis toolpak for finding the last day of the month
Finding the last day of a month in Excel
Formatting cells for dates in Excel
Geeky solution for finding the last day of the month
Last day of the month
Leap year calculation in Excel
MrExcel netcast on finding the last day of the month
Subtracting one from the first day of the next month trick
Using the 0th day in Excel's date function
Using the year and month to find the last day of the month

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152223/

More from this channel