Get the Last Day of the Month in Excel

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.

Sections:

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

Notes

Steps to Get the Last Day of the Month for a Date in Excel

1. Make sure that you have a cell that has a date in it.  You can enter a date directly into the function that we will use but that is often not very helpful on a spreadsheet.
   
   This date here is February 15, 2010.
2. Go to the cell where you want to see the last day of the month and type: =EOMONTH( and then select the cell with the date.
   
3. Now type a comma to move to the next argument in the function.  We will enter a 0 here (I'll talk more about the other options for this argument below).
   
4. Hit Enter and you will see a number like this:
   
   Don't freak out, this is the way that Excel normally stores dates.  Now we just need to format it.
5. To make the number that Excel returned mean something, we need to format the cell as a date.  Select the cell, go to the Home tab, click the drop down box in the Number section, and select Long Date or any desired date format. (For earlier versions of Excel, just right-click the cell and click Format Cells... and select a date format from there.)
   
6. Now, we can see the last date of the month and the actual day of the week for it.
   

There are a few more things to know about how to use this function so keep reading!

Find the Last Day of the Month for a Month in the Future or the Past

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:

Get the Day of the Week from the Last Day of the Month

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.

Return the Number of the 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:

Return the Day of the Week

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.

Notes

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.