How to get the last day of the month, including the date and day of week, for any date in Excel.Â
This method allows you to use any date and get the last day of the month for the month of that date or a future month or a month in the past. This is very helpful when dealing with scheduling and time related scenarios in Excel.
We will use the EOMONTH() function to get the desired result.
Steps to Get the Last Day of the Month for a Date in Excel
Find the Last Day of the Month for a Month in the Future or the Past
Get the Day of the Week from the Last Day of the Month
There are a few more things to know about how to use this function so keep reading!
Using the EOMONTH function, we can find the last day of the month for a month that comes after or before the date that we use in the function.
In Step 3 above we entered 0 for the second argument in the EOMONTH function, which uses the current month of the given date to find the last day of the month.
We can also enter a positive number for that argument to get the last day of the month from a month that comes after the given date or a negative number to get one that comes before the given date.
If we enter 2 for the second argument:
Then we will get the last day of the month for April 2010:
If we use -2 for the second argument:
We will get the last day of the month for December 2009:
One thing that is very useful is to get the actual week day of the last day of the month.
To do this from the results that we got above from the EOMONTH function, we will use two other functions: TEXT and WEEKDAY.
To return the number of the weekday, which is great when using it in other formulas, we use the WEEKDAY function:
This returns:
With the default setting for the WEEKDAY function, Sunday is the first day of the week. If you want to change that so Monday is the first day of the week, enter the function like this:
And you will get this result:
We can use the TEXT function to quickly return the day of the week.
This returns:
You can also use formatting to return the day of the week, but if you don't want to mess with formatting, just use the TEXT function.
Dealing with dates can be a real pain in Excel. The only way to get around it is to learn about dates and time and how they work. Check out all of our date and time tutorials for Excel.
Always remember that Excel stores dates as a serial number, which is why, in Step 4 above, the "date" looks so weird. If you forget that that is a date, you will waste hours trying to "solve" the problem when all you really need to do is to format the cell as a date.
Make sure to download the accompanying spreadsheet for this tutorial so you can follow along and see these examples in action.