EOMONTH

What does this function do?

The EOMONTH function returns the last date of the month, shifted by a specified number of months from the original date.

If the offset is 0, the end of the same month will be returned. If 1 — end of next month, -1 — end of previous month.

Formatting

The result is a date. It can be displayed in standard or custom format. If necessary, you can change the display format via Ctrl + 1.

Syntax

=EOMONTH(start_date, months)

Arguments:

• start_date — required argument. This can be:
  • reference to a cell containing a date
  • result of another function, for example TODAY()
  • text value that Excel recognizes as a date
• months — required integer (can be negative), specifying how many months to move forward or backward

Examples

Triple-click on the formula will select it entirely for copying.

Features

• If the date is entered incorrectly, Excel will return an error.
• Leap years are taken into account: February can have 28 or 29 days.
• The function is useful for calculating periods, financial reports, month-end closing dates, deferral calculations, etc.
• Despite Excel considering 1900 a leap year and having February 29, 1900, the EOMONTH function for all February 1900 dates (even February 29) returns February 28.

Automation via !SEMTools

If you want to get the last date of the month for an entire column at once, you can use the command on the !SEMTools tab.

How to use:

• Select the column with dates;
• Open the !SEMTools tab;
• Go to menu “EXTRACT” – “Attributes” – dates
• Select the “End of month” command.

If the “output result to the right” checkbox is active, a column with the last dates of the corresponding months will appear next to it. Only values are inserted, without formulas.

Other Date and Time functions in Excel

TIMEVALUE, TIME, YEAR, DATE, DATEVALUE, EDATE, DAY, WEEKDAY, YEARFRAC, MONTH, MINUTE, WEEKNUM and WEEKNUM.ISO, WORKDAY, DATEDIF, TODAY, SECOND, NOW, HOUR, NETWORKDAYS

This post is also available in RU.