FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Last Day Of The Month In Excel

Updated on April 29, 2024
Article byWallstreetmojo Team
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Last Day Of The Month In Excel?

Last day of the month in Excel, as the name suggests, is used to find the last date of the given data. To find the last date of the month, we will use the inbuilt function of Excel called the EOMONTH. This function will help us to return the last date of the month. The functionality of this function is not only restricted to knowing the last date of the current month. But, we can choose to know the last day of the previous month or next month. We can also choose to know the last day of the month at the custom-defined gap of months.

For example, suppose we need to achieve the month’s end date from the selected dates in the Excel worksheet in the below example.

Last Day Of The Month In Excel

We can use the Excel EOMONTH function. For this function, we must choose the start date for which we want to calculate the month’s end date. Then select the month’s sequence out of 12, for which we need to find the end date. We will get the result as shown in the below screenshot.

Excel EOMONTH function

Likewise, we can find the last date of the month.

Key Takeaways

  • Last date of the month can be found using Excel’s EOMONTH function. It determines the last day of the current, previous, or next month. One can also specify a custom-defined gap of months to find the last day of the month.
  • The result of the EOMONTH function needs to be formatted as a date in “MM_DD_YY” format.
  • If the format of the “Date” is incorrect, a #NUM error will occur. To calculate the last day of the month, one can use Excel’s TEXT function.

How To Find Last Day Of The Month In Excel?

We can easily find the last date of the given data in Excel with simple steps. Let us learn how to find the last day of the month in Excel with apt examples.

Syntax

Eomonth Formula

Examples To Use EOMONTH In Excel

You can download this Last Day of the Month Excel Template here – Last Day of the Month Excel Template

Example #1 – Last Day Of The Same Month

Below are the steps used to find the last day of the same month:

  1. First, insert the date in the incorrect format. Remember, the default format is “MM_DD_YY.”


    last day of month step 1

  2. Then, insert the formula of EOMONTH.


    last day of month step 2

  3. After the EOMONTH function is inserted, we get the last day in the vale format.


    last day of month step 3

  4. We can now use drag and drop options for the remaining cells.


    last day of month step 3-1

  5. We need to correct the format for this value and set that to date format. So for that, first, select cell B3, then we can go to the “Home” tab, go to “General,” and select a “Short Date” option, as shown in the screenshot below.


    last day of month step 4

  6. After the format is corrected, we may get the last day of the month in Excel.


    last day of month step 5

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Example #2 – Last Date Of Next Month

To calculate the last date of next month.

Last Date of Next Month step 1
  • Step 1 –  We must change the formula and mention “1” instead of “0.”
Last Date of Next Month step 2
  • Step 2 – After the EOMONTH function is inserted, we may get the last day of next month.
Last Date of Next Month step 3
  • Step 3 – We can now use drag and drop options for the remaining cells.
Last Date of Next Month step 4

Example #3 – Last Day Of Previous Month

In this example, we need to specify the last day of the previous month.

Example3 step 1
  • Step 1 – We need to change the formula and mention “month” as “-1” instead of “0.”
Example3 step 2
  • Step 2 – Now that we have entered the EOMONTH function, press Enter key. We will obtain the last date of the month as shown in the below image.
Example3 step 3
  • Step 3 – We can now use drag and drop options for the remaining cells.
Example3 step 4

Example #4 – Last Day of the Month as Per Custom Month Gap

In this case, we need to make the “month” argument dynamic instead of a static one, and all the arguments will be the same as mentioned in the above steps.

Example4 step 1
  • Step 1 – To calculate the last day of the month in excel, we just need to change the formula.
Example4 step 2
  • Step 2 – After the EOMONTH function is inserted, we will get the last day of the month as per the custom “Months Gap.”
Example4 step 3
  • Step 3 – We can now use drag and drop options for the remaining cells.
Example4 step 4

Important Things To Note

  • The result of EOMONTH will not be formatted as a date; the result needs to be formatted as a date.
  • The “Date” should be in the “MM_DD_YY” format.
  • If the “Date” is not an incorrect format, there will be a #NUM error.
  • We can calculate the day that falls on the last date of the month by using the TEXT function of Excel.

Frequently Asked Questions

1. How do you conditional format the last day of the month in Excel?

To apply formatting to a date cell, please proceed to the Home tab and then click on ‘Conditional Formatting.’ Next, choose the option for ‘Highlight Cell Rules’ and select ‘A Date Occurring.’ You can then choose one of the date options from the drop-down menu on the left-hand side of the window. The options available range from last month to next month. By following these steps, the formatting will be applied to the date cell, allowing for a more organized and professional.

2. How do you check if the date is the last day of the month in Excel VBA?

Setting the day argument to zero in VBA’s DateSerial function returns the last day of the month. However, if you want to get the last day of the current month, you need to add 1 to the month argument. Otherwise, the function will return the last day of the previous month.

3. How do you find the last working day of the month in Excel?

To find the last business day of the month for the date in cell A2, one may use a combination of three functions:
1. Use the EOMONTH function to get the last day of the month for the date in cell A2.
2. Add 1 to the result, which gives us the first day of the next month.
Use the WORKDAY function to go back to one workday (or “business” day), which gives us the last business day of the month for the date in cell A2.
For example, let’s assume that we have to find the last date of the following data


EOMONTH function example

Now, using EOMONTH function in Excel, we can obtain the result as shown in the below image.

EOMONTH function example output

This article is a guide to Finding the Last Day of the Month. We discuss finding the last day of the month in Excel using the EOMONTH function and examples and a downloadable template. You may learn more about Excel from the following articles: –

Reader Interactions

Comments

  1. Yasap Popoitai says

    Thank you very much. I learnt a lot. An excellent tutorial. So easy to follow.

Leave a Reply

Your email address will not be published. Required fields are marked *