This article explains the use of Excel EOMONTH function in depth. You will see examples demonstrating how to get the last date of the month, last date before or after Nth month from a specified date, calculate remaining days in a month from a date etc.
EOMONTH Function and Its’ Uses
The Excel EOMONTH function returns the serial number of the last day of a month that is before or after a specified number of months from a starting date. In other words, it simply returns end of the month’s date after adding a specified number of months.
For instance, the following function would return the answer as 30-Apr-18 because the same is last day of date supplied in a function.
You can also use this function to get the number of days in the particular month from an initial date or find out the last weekday in a month etc.
EOMONTH (start_date, months)
- Start_date: A valid date representing initial or start date.
- months: The number of months to offset from the start_date. Positive value returns the date in the future and negative value returns the past date.
Note: Both of the arguments are required by the function.
Examples: – Excel EOMONTH Function
After having the understanding of syntax and arguments of EOMONTH function, it is the time to explore the function with several examples. The following examples help in getting more clarity on this function.
Example 1: Get the last day of the month
Situation 1: Returns the last day of the current month
If you are required to return the last day of the current month, there is no need to refer to any cell or writing date manually to supply start_date. It can be supplied using TODAY function which returns the serial number of current date formatted as a date. Enter the following formula in any cell.
=EOMONTH (TODAY(), 0)
Tip: EOMONTH returns the serial number that represents the date, you can use custom date format to a cell(s) to display as valid excel date.
Situation 2: Returns the last day of the month based on specified initial date
Assuming you have columns with the date in column A and you are required to return the last day of the month for each given date, you can enter the following function in cell B2:
Example 2: How to get last day of the next/previous month
And when you want to find the last day of the next month or previous month, make small changes in the function as follow:
=EOMONTH( E3, 1) – returns the serial number of last day of the month, one month after the start date in cell E3.
=EOMONTH( E3, -1) – returns the serial number of last day of the month, one month before the start date in cell E3.
Tip: Set the specified number of months to get the last date in future or past.
Example 3: Calculate the remaining days in a month
To calculate the total number of days are left in a month from a date, you simply subtract the start date from the date returned by the EOMONTH function as follow in cell C3:
=EOMONTH (B3, 0) – B3
As the function returns the serial number of the last date, start date provided in cell B3 was subtracted from the last day. Since Excel stores date as the serial number, when a serial number of 11-Jan-18 was subtracted from 31-Jan-18, result 20 is returned in the form of Excel valid date which we converted into a normal number by applying general format.
Tip: You can either use keyboard shortcut CTRL + SHIFT + ~ or use Format Cells dialog box to apply the general format to a cell(s).
- EOMONTH returns a serial number representing a date. As Excel stores date in sequential serial number where the serial number 1 represents 1/1/1900 and display valid Excel date because of default date format. For more info, visit Microsoft Website.
- When decimal value supplied to months argument, function only add the integer portion to start_date and ignore the decimal fraction.
- #VALUE! error is returned if months argument is not supplied as a numerical value.
- Function returns #NUM! error
- If the supplied start date is not a valid Excel date
- Final result after adding the required number of months is not a valid Excel date.