How to Find Last Day of the Month in Excel?

For example, suppose we need to achieve the month’s end date from the selected dates in the Excel worksheet; we can use the Excel EOMONTH function. For this function, we must choose the start date from which the month’s end date is to be calculated and then select the month’s sequence out of 12, for which we need to find the end date.

Syntax

Examples to use EOMONTH in Excel

Example #1 – Last Day of the Same Month

Example #2 – Last Date of Next Month

To calculate the last date of next month.

  • First, insert the date in the incorrect format. Remember, the default format is “MM_DD_YY.” Then, insert the formula of EOMONTH. After the EOMONTH function is inserted, we get the last day in the vale format. We can now use drag and drop options for the remaining cells. 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. After the format is corrected, we may get the last day of the month in Excel.

  • Step 1 – We must change the formula and mention “1” instead of “0.”

  • Step 2 – After the EOMONTH functionEOMONTH FunctionEOMONTH is a worksheet date function in excel which calculates the end of the month for the given date by adding a specified number of months to the arguments. This function takes two arguments: the date and another as integer, and the output is in the date format.read more is inserted, we may get the last day of next month.

  • Step 3 – We can now use drag and drop options for the remaining cells.

Example#3 – Last Day of Previous Month

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

  • Step 1 – We need to change the formula and mention “month” as “-1” instead of “0.”

  • Step 2 – After the EOMONTH function is inserted, we may get the last day of the previous month.

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.

  • Step 1 – To calculate the last day of the month in excel, we just need to change the formula.

  • Step 2 – After the EOMONTH function is inserted, we will get the last day of the month as per the custom “Months Gap.”

Things to Remember

  • 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.

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: –

  • Date Function in VBADate Function In VBAVBA Date is a date and time function. It returns only the current date as per the system date you are using and has no arguments whatsoever. This function returns the current system date.read moreHow to Convert Date to Text in Excel?How To Convert Date To Text In Excel?To convert a date to text in Excel, right-click on the date cell and select the format cells option. A new window will open. You can convert the date to text by selecting the desired format from a list of options.read moreEDATE Function in ExcelEDATE Function In ExcelEDATE is a date and time function in excel which adds a given number of months into a date and gives us a date in a numerical format of date. It takes dates and integers as input, the output returned by this function is also a date value. read moreUsing DATE Function in ExcelUsing DATE Function In ExcelThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as integers.read moreExponents in ExcelExponents In ExcelIn Excel, exponents are the same exponential function as in mathematics, where a number is raised to a power or exponent of another number. Exponents can be used by two methods: the power function or the exponent symbol on the keyboard.read more