Table of Contents
How to Find Last Day of the Month in Excel?
We are always eager to know the first day of the month but sometimes we often need to know the last day of the month in excel. This is specifically happening in the case where we have to pay some dues at the end of the month and we want to know, how much time do we still have before the debt becomes due?
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.
Last Day of the Month in Excel using EOMONTH Formula
The generic EOMONTH is defined as
EOMONTH is also the inbuilt functions in excel that belongs to the “Date & Time” class of functions. This function does not need much of arguments as input to give us the last date of the month. We just need to enter the beginning date and then specify the month for which we need the last date of the month.
=EOMONTH (Date, Months) or = EOMONTH (1/1/19,0)
This function will return the last date of the same month that is Jan in this case. Since we have specified “0” in the months excel will calculate the last date for the same month of Jan.
= EOMONTH (Date, Months+1) or = EOMONTH (1/1/19,1)
- Since now we have specified “1” in the month’s arguments, Excel will calculate the last date of the next month.
- Since Jan is the month of the date, excel will add one month to Jan and will give us the last date of FEB month.
How to Use Last Day of the Month in Excel?
Below are the Examples of Using Last Day of the Month in Excel.
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.
You can now use drag and drop option for the remaining cells.
Step 4 – 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 5 – 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.
we just need to change the formula and mention “1” instead of “0”.
After the EOMONTH function is inserted we will get the last day of next month.
You can now use drag and drop option 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
We just need to change the formula and mention “month” as “-1” instead of “0”.
After the EOMONTH function is inserted we will get the last day of the previous month.
You can now use drag and drop option 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 same as mentioned in the above steps.
To calculate the last day of the month in excel, we just need to change the formula
After the EOMONTH function is inserted we will get the last day of the month as per custom month Gap.
You can now use drag and drop option for the remaining cells.
Things to Remember about Last Day of Month in Excel
- 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 “MM_DD_YY” format.
- If the “Date” is not incorrect format, there will be the #NUM error.
- We can calculate the day that falls on the last date of the month by using the “Text” function of Excel.
You can download this Last Day of the Month Excel Template here – Last Day of the Month Excel Template
Recommended Articles
This has been a guide to Last Day of the Month Excel. Here we discuss how to Find Last Day of the Month in Excel using EOMONTH function along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
Leave a Reply