Date & Time

EOMONTH Function in Excel

Returns the last day of the month, a specified number of months before or after a date.

Syntax

  • =EOMONTH(start_date, months)

Arguments

  • start_date (required): The starting date
  • months (required): Months to add (negative for past)

Examples

  • =EOMONTH(TODAY(), 0) - Last day of current month - Result: 1/31/2026
  • =EOMONTH(A1, 3) - Last day 3 months from now - Result: End of month
  • =EOMONTH(A1, -1)+1 - First day of current month - Result: Start of month

Excel EOMONTH function documentation

  • EOMONTH returns the last day of the month that is a specified number of months before or after a date.
  • Syntax: =EOMONTH(start_date, months). Essential for month-end reporting and accruals.
  • months = 0 gives last day of start_date's month; -1 gives last day of prior month.
  • Related: [EDATE](/functions/edate/), [DATE](/functions/date/), [EOMONTH in aging reports](/categories/financial/).

EOMONTH syntax and month-end logic

  • Handles varying month lengths — Jan 31 + 1 month logic handled via EOMONTH not EDATE alone.
  • months can be negative for prior period cutoffs.
  • Result is a date serial at last moment of that calendar day.
  • Pair with [SUMIFS](/functions/sumifs/) where date column <= EOMONTH(TODAY(),0) for MTD/QTD.

Step-by-step: rolling month-end balance date

  • Step 1 — Transaction dates in column A.
  • Step 2 — Period end label: =EOMONTH(A2, 0) returns month-end for each row's month.
  • Step 3 — Prior month-end: =EOMONTH(TODAY(), -1).
  • Step 4 — Use in pivot or SUMIFS on helper column for monthly aggregates.
  • Step 5 — Format as yyyy-mm-dd for audit-friendly reports.

EOMONTH vs EDATE vs end-of-month tricks

  • EOMONTH — always last calendar day of target month.
  • EDATE — same day of month N months away (may not be month-end).
  • DATE(Y,M+1,0) — legacy trick for month-end without EOMONTH.
  • Financial close: EOMONTH(TODAY(),-1) is standard prior month-end reference.

Worked examples to copy

  • This month-end: =EOMONTH(TODAY(), 0).
  • Next month-end: =EOMONTH(TODAY(), 1).
  • Same month-end as date in A2: =EOMONTH(A2, 0).
  • Aging bucket end: =EOMONTH(invoice_date, 0)+60 for 60 days after month-end policy.

People also ask

  • EOMONTH vs EDATE? — EOMONTH returns last day of month; EDATE shifts by months keeping day.
  • EOMONTH negative months? — Yes. EOMONTH(TODAY(),-1) is prior month-end.
  • EOMONTH leap year? — Returns Feb 28 or 29 correctly as month-end.
  • EOMONTH in SUMIFS? — Common cutoff: transactions <= EOMONTH(TODAY(),0).

Common errors

  • Returns date serial number
  • Format cell as date

Use cases

  • Month-end reporting
  • Payment due dates
  • Period calculations

Frequently asked questions

  • How do I get the first day of a month? Use =EOMONTH(A1, -1)+1. This gets the last day of the previous month and adds 1. Or =DATE(YEAR(A1), MONTH(A1), 1).
  • How do I calculate month-end dates for reporting? =EOMONTH(TODAY(), 0) for current month end. =EOMONTH(TODAY(), -1) for last month end. Use in date ranges for monthly reports.
  • What happens with months=0? EOMONTH(date, 0) returns the last day of the same month as the input date. Useful for finding month-end regardless of input day.
  • How do I get the last business day of the month? Use =WORKDAY(EOMONTH(A1,0)+1, -1). This goes to first of next month, then back one workday. Add holidays parameter if needed.
  • Can EOMONTH handle negative months? Yes! =EOMONTH(A1, -3) returns the last day of the month 3 months before A1. Useful for historical period calculations.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use EOMONTH

  • Month-end reporting — common Date & Time scenario for EOMONTH.
  • Payment due dates — common Date & Time scenario for EOMONTH.
  • Period calculations — common Date & Time scenario for EOMONTH.

EOMONTH in the Date & Time category

  • Browse all Date & Time functions at /categories/date-time/ for related formulas.
  • EOMONTH syntax: =EOMONTH(start_date, months)
  • start_date (required): The starting date
  • months (required): Months to add (negative for past)
  • Confirm EOMONTH arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm EOMONTH arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • EDATE (/functions/edate/): Returns a date that is a specified number of months before or after a given date.
  • DATE (/functions/date/): Creates a date from year, month, and day components.
  • MONTH (/functions/month/): Returns the month of a date as a number from 1 to 12.
  • DAY (/functions/day/): Returns the day of a date as a number from 1 to 31.

Errors to watch for

  • Returns date serial number — review causes on linked error pages in the directory.
  • Format cell as date — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter EOMONTH on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

EOMONTH worked examples to copy

  • =EOMONTH(TODAY(), 0) — Last day of current month. Expected result: 1/31/2026.
  • =EOMONTH(A1, 3) — Last day 3 months from now. Expected result: End of month.
  • =EOMONTH(A1, -1)+1 — First day of current month. Expected result: Start of month.

EOMONTH reference summary for crawlers and offline review

  • EOMONTH belongs to the Date & Time category in Excel. Returns the last day of the month, a specified number of months before or after a date.
  • Full syntax: =EOMONTH(start_date, months). Open /functions/eomonth/ for parameters, FAQs, and related pages.
  • Common mistakes: Returns date serial number; Format cell as date
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.