Look Who it Was Random video

Excel - Excel Tutorial: How to Find the Last Day of the Month using the Date Function - Episode 559

268 views · Published 20 July 2009 · 2:30 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  How to Find the Last Day of the Month using the Date Function.

Welcome back to the MrExcel netcast! In today's episode, we'll be discussing the date function and its versatility. I recently received a note from Eric about episode 540 where I used the date function to find the last day of the current month. Let's take a closer look at this function and how it can be used in various scenarios.

The date function requires us to specify a year, month, and day. For example, if we enter the year 2007, the month of 7, and the day of 27, the function will return the date of this podcast, which is July 27. We can also format the cell to display the day of the week, which in this case is a Friday. This formula can be copied down to other rows to see how it works for different dates.

But here's where it gets interesting - the date function can handle dates that don't actually exist. For instance, if we enter the 0th day of August, it will automatically give us the last day of July. Similarly, we can use negative numbers to find a date before the last day of the month. For example, the negative sixth day of August will give us a week before the last day of July. The date function is also smart enough to handle dates in the future, such as the 15th month and the 45th day, which will give us April 14th of next year.

In episode 540, I used a different method to find the last day of the month, but thanks to Eric's note, I have found a better way to do it. Instead of using the year and month of the date and then adding one and subtracting one, we can simply use the 0th day of the following month. This will automatically give us the last day of the current month, regardless of how many days are in that month. So, for example, if we enter =DATE(YEAR(C485),MONTH(C485)+1,0), it will convert May 7th to May 31st. This is a much simpler and more efficient way to find the last day of the month.

I want to thank Eric for his note and as a token of appreciation, I will be sending him and everyone else who sends in a tip this week, one of my Excel master pins. Thank you for tuning in to this week's netcast from MrExcel. Don't forget to subscribe to our channel and hit the notification bell to stay updated on our latest videos. See you next week for another informative episode!

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/

Table of Contents:
(00:00) Introduction by Bill Jelen
(00:10) Note from Eric about episode 540
(00:20) Explanation of the date function
(00:30) Example of using the date function to find the current date
(00:40) Formatting the date and copying the formula
(00:51) Using the date function to find the first day of a specific month
(01:01) Versatility of the date function
(01:31) Improved method for finding the last day of the month
(01:57) Thanking Eric and giving out Excel master pins
(02:11) 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
Day of the week
Excel master pins
First day of the month
Formatting dates
Handling non-existent dates
Improving on episode 540
Last day of the current month
Negative day values
Versatility of the date function

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



Erik points out that the best way to find the last day of this month is to ask for the zeroth day of next month. Episode 559 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!

More from this channel