|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

EOMONTH

EOMONTH

  • Last updated on

Description

Use this function to return the last day of the month, n months in the past or future. This is useful for calculating maturity dates or due dates that fall on the last day of the month.

 

Syntax

(start_date, months)

 

Inputs

This function has the following arguments:

Name Required Description Valid input
start_date Yes The date that the result is to be calculated from. A cell reference, an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD. A formula which results in one of these is also acceptable.
months Yes The number of months before or after start_date. A positive value months yields a future date; a negative value yields a past date. A cell reference or an integer. A formula which results in either of these is also acceptable.

 

Example

Sample data

  A B C
1 Date Adjustment EOMONTH Result
2 February 1, 2024 14 4/30/2019
3 April 23, 2019 23 5/31/2017
4 7/19/2003 222 44592
5 12/23/2023 -24 #NUM!

Sample formulas

Use case Formula Explanation and Result
Add a set number of months to a date specified in a cell, and change the day to the last day of that month. =EOMONTH(A2,14)

This formula adds 14 months to the date in cell A2 and changes the day to the last day in the month.

Result:: 45777 (4/30/2025)
Subtract a number of months from a specified date, and change the day to the last day of that month. =EOMONTH("7/19/2023",4)

This formula adds 4 months to the supplied date of "7/19/2023", and changes the day to the last day in the month.

Result: 45260 (11/30/2023)
Add the number of months in one cell to a date specified in a cell, and change the day to the last day of that month. =EOMONTH(A5,B5)

This formula adds the number of months in cell B5 (-24) to the date in cell A5.

Result: the #NUM! error because the value in B5 is negative.
Calculate the last business day of the month. =WORKDAY(EOMONTH(A2,0), -1)

This formula works in the following manner:

  • A1: Contains the date for which to calculate the last business day of the month.
  • EOMONTH(A1,0): Calculates the last day of the month for the given date.
  • WORKDAY(…, -1): Returns the last working day before the last day of the month. (This effectively skips the weekend if the last day falls on a Saturday or Sunday).

Result: 45350 (2/28/2024)
Calculate the next payment date for a quarterly subscription, ensuring the payment always falls on the last day of the month. =EOMONTH(EDATE(A3, 3), 0)

This formula works in the following manner:

  • A1: Contains the starting date of the subscription (or the date of the preceding payment).
  • EDATE(A1, 3): Calculates the date exactly three months after the given date.
  • EOMONTH(…, 0): Takes the result of EDATE (which might not be the last day of the month) and adjusts it to the last day of that same month.

Result: 43677 (7/31/2019)

 

Notes

  • The Workiva platform stores dates as numbers so they can be used in calculations.
    By default, January 1, 1900 = 1.
  • EOMONTH returns a serial number date value, which can then be formatted as a date.
  • If the supplied or referenced start_date isn't a valid integer date or in the format DD/MM/YYYY or YYYY/MM/DD, EOMONTH returns the #VALUE! error.
  • If start_date plus months is not a valid date (typically this will be because it is out of range), EOMONTH returns the #NUM! error.
  • If start_date has a fractional time attached (that is, isn't an integer), it's removed.
  • If months isn't an integer (it contains a decimal value), it's truncated.
  • Wildcards don't work with this function.

 

Related functions

DescriptionUse this function to return the last day of the month, n months in the past or future. This is useful for ...