EOMONTH function

Returns the date of the last day of the month that is a specified number of months before or after a given start date.

=EOMONTH(start_date, months)

Generate a EOMONTH formula

Describe what you need. The generator will reach for EOMONTH where EOMONTH is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How EOMONTH reads its arguments
start_daterequiredmonthsrequiredEOMONTH →
ArgumentRequiredDescription
start_dateRequiredA date value or text string representing the starting date; if text, the format must be recognizable by your spreadsheet app's locale settings or wrapped in DATEVALUE().
monthsRequiredAn integer representing the count of months to move forward (positive values) or backward (negative values); must be numeric, not text, and typically between -120 and +120 to stay within valid date ranges.

Returns

A date value (formatted as YYYY-MM-DD) representing the final day of the target month.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the end of the current billing month

Order IDOrder DateBilling Period End
10032025-02-08=EOMONTH(B2,0)
10042025-02-28=EOMONTH(B3,0)
=EOMONTH(B2, 0)

Result: 2025-02-28 (for the Feb 8 order); 2025-02-28 (for the Feb 28 order)

When months is set to 0, EOMONTH returns the last day of the same month as start_date. Both orders—whether placed on the 8th or the 28th—have their billing deadline as February 28, 2025. This is useful for teams needing to know when invoices are due or when month-end reconciliation closes.

2. Calculate end-of-quarter reporting deadline

Order IDOrder DateQuarter End
10012025-01-15=EOMONTH(B2,2)
10052025-03-10=EOMONTH(B3,2)
=EOMONTH(B2, 2)

Result: 2025-03-31 (from Jan 15); 2025-05-31 (from Mar 10)

Setting months to 2 moves the end-of-month calculation forward by 2 months. An order from January 15 reaches its quarter-end deadline on March 31 (Q1 close), while a March 10 order extends to May 31. This pattern helps project fiscal milestones or compliance deadlines from order dates.

3. Find the previous month-end for historical reconciliation

Order IDOrder DatePrevious Month End
10052025-03-10=EOMONTH(B2,-1)
10062025-03-25=EOMONTH(B3,-1)
=EOMONTH(B2, -1)

Result: 2025-02-28 (from Mar 10); 2025-02-28 (from Mar 25)

Negative months move backward in time. Both March orders reference February 28, 2025 as the prior period close. Accounting teams use this to pull historical data for reconciliation, cutoff verification, or to align transactions with the previous closed period.

Common errors

Which EOMONTH error are you seeing?
EOMONTH returned an error#VALUE!
Wrap the text in DATEVALUE(): =EOMONTH(DATEVALUE('Jan 15 2025'), 0) or use DATE(): =EOMONTH(DATE(2025,1,15), 0)
#VALUE!
Remove the quotes: change =EOMONTH(DATE(2025,2,8), "1") to =EOMONTH(DATE(2025,2,8), 1)
#NUM!
Use a reasonable offset within ±120 months, or reconsider the business logic; if you need multi-year projections, break them into smaller steps.
ErrorWhy it happensHow to fix it
#VALUE!start_date is text in an unrecognized format, such as 'Jan 15 2025' without DATE() conversion or DATEVALUE().Wrap the text in DATEVALUE(): =EOMONTH(DATEVALUE('Jan 15 2025'), 0) or use DATE(): =EOMONTH(DATE(2025,1,15), 0)
#VALUE!months argument is quoted text ('1' or "1") instead of a bare number, causing the spreadsheet to treat it as a string.Remove the quotes: change =EOMONTH(DATE(2025,2,8), "1") to =EOMONTH(DATE(2025,2,8), 1)
#NUM!The months offset would create a date outside the valid range (typically year 1900–9999 in most spreadsheet apps), such as adding 10000 months to a recent date.Use a reasonable offset within ±120 months, or reconsider the business logic; if you need multi-year projections, break them into smaller steps.

Tips and when to use something else

  • Use negative months to find past period ends: EOMONTH(today, -1) gives last month's final day, useful for cycle-end reporting and reconciliation.
  • Combine EOMONTH with IF to create period-boundary logic: =IF(A2<=EOMONTH(A2,0), "On-Time", "Late") checks if a date falls before month-end.
  • If you need the first day of a month instead of the last, use =DATE(YEAR(EOMONTH(start,0)), MONTH(EOMONTH(start,0)), 1) or add 1 day to the prior month's end.
  • Consider DATEDIF if you need the count of days between periods rather than just the date of month-end; EDATE is useful when you need the same calendar day in a different month rather than the final day.

Related functions

EDATEReturns a date that is a specified number of months before or after a start date, making it easy to calculate contract expirations and probation deadlines.DATEDIFReturns the number of time units (days, months, years) between two dates, useful for calculating durations, aging, or time-to-deadline.DATEDATE returns a date serial number from year, month, and day arguments, enabling date construction from separate numeric components.TODAYTODAY returns the current date as a date value, automatically updating each time the spreadsheet opens or recalculates.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

Can EOMONTH go backward in time?
Yes. Use negative values in the months parameter: =EOMONTH(DATE(2025,3,15), -1) returns 2025-02-28. This is essential for historical analysis and prior-period lookups in accounting.
What is the difference between EOMONTH and EDATE?
EOMONTH returns the last day of the target month (always the 28th, 29th, 30th, or 31st). EDATE returns the same day of the month in the target period—for example, EDATE(2025-01-15, 1) returns 2025-02-15, not 2025-02-28. Use EOMONTH for period-end deadlines and EDATE for same-day anniversaries.
How do I get the first day of a month using EOMONTH?
EOMONTH only returns the last day, but you can get the first day by subtracting 1 from EOMONTH of the previous month: =EOMONTH(start_date, -1)+1. Alternatively, use DATE(YEAR(start_date), MONTH(start_date), 1) for the first day of the current month.
Why does my EOMONTH result look like a number instead of a date?
Spreadsheet apps store dates as numbers internally (date serials). If you see 45351 instead of 2025-02-28, your column is formatted as 'Number' instead of 'Date'. Right-click the cell, select Format Cells, and change to Date format, or apply the cell formatting shortcut in your app.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17