How to Find Last Day of the Month in Excel?
In such cases, we will use the inbuilt function of excel that is 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 know the last date of the current month but we can choose to know the last day of the previous month in excel, next month and we can also choose to know the last day of the month at the Custom defined gap of months.
Syntax
Examples
Example #1 – Last Day of the Same Month in Excel
- Step 1 – Insert the Date incorrect format, remember the default format is “MM_DD_YY”.
- Step 2 – Enter the formula of EOMONTH.
- Step 3 – After the EOMONTH function is inserted we will get the last day in the vale format.
- Step 4 – You can now use drag and drop option for the remaining cells.
- Step 5 – Now we need to correct the format for this value and set that to date format. For that first, select the cell B3 then you can go to the Home Tab then go to general and select a short date option as shown in the below screenshot.
- Step 6 – After the format is corrected we will get the last day of the month in excel.
Example #2 – Last Date of Next Month in Excel
To calculate the last date of next month.
- Step 1 – we just need to change the formula and mention “1” instead of “0”.
- Step 2 – After the EOMONTH function is inserted we will get the last day of next month.
- Step 3 – You can now use drag and drop options for the remaining cells.
Example#3 – Last Day of Previous Month in Excel
In this example, we need to specify the last day of the previous month
- Step 1 – We just need to change the formula and mention “month” as “-1” instead of “0”.
- Step 2 – After the EOMONTH function is inserted we will get the last day of the previous month.
- Step 3 – You can now use drag and drop options for the remaining cells.
Example #4 – Last Day of the Month as Per Custom Month Gap
In this case, we need to make the “month “Argument as dynamic instead of 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 custom month Gap.
- Step 3 – You can now use drag and drop options for the remaining cells.
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.
Recommended Articles
This has been a guide to the Last Day of the Month Excel. Here we discuss how to Find Last Day of the Month in Excel using EOMONTH function along with examples and excel template. You may learn more about excel from the following articles –
- DATEVALUE in Excel VBA
- Date Function in VBA
- Formula with Text in Excel
- How to Convert Date to Text in Excel?
- Use EDATE Excel Function
- DATEVALUE Function in Excel
- Using DATE Function in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
