What is the difference between EDATE and EOMONTH?

EDATE returns the same day of the month N months before or after the start date, while EOMONTH returns the last day of the month N months before or after the start date.

What happens if one month later from the 31st is the 30th or 28th?

EDATE automatically adjusts to the last day of the month (January 31 + 1 month = the last day of February). If you need the last day of the month, use EOMONTH from the start.

What if dates are stored as text and calculations do not work?

Convert them with DATEVALUE, or combine separate year, month, and day values with DATE(Y,M,D). For data with mixed regional formats, standardize it with TEXT first and then use DATEVALUE.

What if I need the day after month-end (the first day of the next month)?

Use EOMONTH(start_date,months)+1. For example: =EOMONTH(A2,0)+1

Use Excel DATE, EOMONTH, and EDATE to find month-end dates, calculate billing dates plus month-end, and calculate dates N months ahead or back. Covers common pitfalls such as month-end rollover, the 1900/1904 date systems, and text dates, along with practical examples and best practices.

Master Date Calculations with DATE, EOMONTH, and EDATE (Month-End, N Months Later, and Last Days in 1 Minute)

CONTENTS

Master Date Calculations with DATE, EOMONTH, and EDATE (Month-End, N Months Later, and Last Days in 1 Minute)

Contract expiration dates, settlement dates, billing cycles—calculate them automatically with DATE, EDATE, and EOMONTH instead of checking a calendar. They handle month-end dates, leap years, and varying month lengths cleanly.

Core Concepts and Syntax

Function	Syntax	Description
DATE	`=DATE(year, month, day)`	Combines year, month, and day to create a valid date (automatically adjusts month/day overflow)
EDATE	`=EDATE(start_date, months)`	Returns the same day of the month a specified number of months before or after the start date (adjusts to month-end when needed)
EOMONTH	`=EOMONTH(start_date, months)`	Returns the last day of the month a specified number of months before or after the start date

TIP: Keep calculations as date values, and separate the display format with TEXT(date,"yyyy-mm-dd").

Create Dates with DATE

Example 1) Data with separate year, month, and day values

=DATE(A2, B2, C2)

Example 2) Automatic adjustment for month/day overflow

=DATE(2025, 2, 30)   // → 2025-03-02
=DATE(2025, 13, 5)   // → 2026-01-05

N Months Before or After — EDATE

Example 3) A 12-month term from the contract start date

=EDATE(A2, 12)

Example 4) Calculate a payment date (the same day each month)

=EDATE(A2, 1)   // Same day next month
=EDATE(A2, -1)  // Same day last month

Important Dates that do not exist, such as the 31st, are automatically adjusted to the last day of the month.

Find the Last Day of the Month — EOMONTH

Example 5) This month’s last day / the first day of next month

=EOMONTH(TODAY(), 0)      // Last day of this month
=EOMONTH(TODAY(), 0) + 1  // First day of next month

Example 6) The last day of the month three months after the start date

=EOMONTH(A2, 3)

6 Practical Templates

① Billing cycle: Bill on the last day of the month after the transaction date

=EOMONTH(A2, 1)

② Settlement cycle: Settle at the end of the current month; carry unsettled items forward

=IF(A2<=EOMONTH(TODAY(),0),"Settlement due","Next month")

③ Subscription expiration date (start date + N months)

=EDATE(A2, N)

④ Quarter-end date

=EOMONTH(DATE(YEAR(A2), CEILING(MONTH(A2),3), 1), 0)

⑤ Find the first day of the month

=EOMONTH(A2, -1) + 1

⑥ Normalize a text date (to yyyy-mm-dd)

=TEXT(DATEVALUE(A2), "yyyy-mm-dd")

If regional formats are mixed, first standardize the separators with SUBSTITUTE.

Common Mistakes and Pitfalls

Text dates → If values are left-aligned in cells, they may be text. Convert them with DATEVALUE.
Rollover misunderstanding → January 31 + 1 month = the last day of February (automatic EDATE adjustment). Confirm that this matches your intent.
1900/1904 date systems → Dates can differ by four years when moving between files that use different date systems. Keep the File ▸ Options ▸ Advanced ▸ Use 1904 date system setting consistent.
Separate display from calculation → Use date values for calculations and TEXT for display. Do not use TEXT results in subsequent calculations.

Summary

Goal	Recommended Formula
N months later or earlier	`EDATE(start_date, months)`
Last day of the month	`EOMONTH(start_date, months)`
First day of the month	`EOMONTH(start_date,-1)+1`
Combine year, month, and day	`DATE(Y,M,D)`

FAQ

What date is N workdays after month-end?

=WORKDAY(EOMONTH(A2,0), N) (you can add a holiday range as the third argument)

Are leap-year February dates handled automatically?

Yes. DATE, EDATE, and EOMONTH automatically apply leap-year rules.

Choose a column in your current report that needs a month-end date, month-start date, or date N months later, and apply one of the templates above right away. Automating date calculations reduces deadline stress.

TEXTSPLIT · TEXTBEFORE · TEXTAFTER로 텍스트 분리/추출 끝! (정규식 없이 1분 컷)