Function: EOMONTH

by | Jun 19, 2019 | Excel Formulas

eomonth

The EOMONTH Excel function (end of month) is a Date & Time formula that calculates and returns the serial date number of the last day of the month, for a certain number of months before or after a given date. EOMONTH function is useful for calculating maturity dates or due dates that fall on the last day of the month as the date of issue. In this guide, we’re going to show you how to use the EOMONTH function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

EOMONTH Excel Function Syntax

EOMONTH(start_date, months)

Arguments

start_date The start date. Microsoft recommends using the DATE or other similar functions that return a date serial number.
months

Number of months before or after the start_date.

·         Use a positive integer for future dates

·         Use a negative integer for past dates

Examples

Date after the start date

All arguments of the EOMONTH function are required. Begin with the start_date by entering a date value from a formula. You can use the DATE function to generate a date in numeric format. Enter a positive number for the months argument to point to the last day of the month of a future date.

=EOMONTH(DATE(2018,1,12),5)

Date before the start date

To indicate a date before the start_date, use a negative number for the months argument.

=EOMONTH(DATE(2018,1,12),-1)

Last day of the current month

You can use the EOMONTH function to find the last day of the current month. To do this, use 0 for the month argument.

=EOMONTH(DATE(2018,1,12),0)

Download Workbook

Tips

  • Use the EDATE function to calculate dates that don't need to land on the last day of a month.
  • EOMONTH function is useful for calculating expiration dates, maturity dates, and other due dates.
  • Excel truncates the months argument. For example, either 1 or 1.7 mean same.
  • Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.

Issues

  • If start_date is not a valid date, EOMONTH function returns the #VALUE! error value. Microsoft recommends use a formula to generate a valid date serial value.