Get last day of the month from a specified date
Many times, we need to get the last date of a month, from a specified date. This is not always so convenient- counting one after the other, and there are chances for error- which makes it even worse. Now, getting the last day of a month from a specified date, could be less tasking and substantially easy using the EOMONTH function.
The syntax for the Excel EOMONTH function is:
It has the following argument.
- start_date (required) – The starting date to use in the calculation.
- months (required) – The number of months to add to the start_date. It can be a positive or negative value.
In the EOMONTH formula, as shown above, the second character set in bracket signifies the last day of a month in prospect, or another in the past.
Here are some explanatory instances:
- When a zero is inserted for months (as shown in the formula), the EOMONTH calculator would show the last day of the current month.
- To get last day of the next month, insert numeral 1 on the month character spot, to look like this:
= EOMONTH (date, 1)
- To get the last day of the previous month before the current, let formula look like this:
= EOMONTH (date, -1)
The EOMONTH calculator shows its results in numeric codes, see more details of how to get
last day of the month in the image below:
Figure 1. EOMONTH Description in Excel
Alternatively, however, users may formulate a formula to look like the one below, with inputs showing the date, year and month that is desired. Every other format meanings remain the same:
=DATE(YEAR(date),MONTH(date) +1 ,0)
Note that with this alternative method to EOMONTH use, imputing a zero for the DATE brings out an output showing the day to the last of the previous month to the current. Subsequently, when the user adds a numeral 1 to the month and leaves a zero for the ‘day’ character, the entire DATE would consequently show the last day of the current month.