Can EDATE handle negative months?

Yes, EDATE subtracts months when you pass a negative value. For example, =EDATE(DATE(2024,6,15), -3) returns March 15, 2024, going backward three months. This is useful for calculating eligibility start dates or lookback periods.

Why does EDATE give Feb 28 instead of Feb 31 when I add months to Jan 31?

EDATE intelligently handles months with fewer days. When the target month cannot accommodate the original day, it returns the last valid day of that month. This prevents errors and ensures dates remain valid year after year.

Can I use EDATE with text dates like "2023-11-01"?

EDATE requires a date value or serial number, not text. If you have text dates, convert them first with DATEVALUE: =EDATE(DATEVALUE("2023-11-01"), 6). Alternatively, use DATE to build a date: =EDATE(DATE(2023,11,1), 6).

How do I calculate a contract expiration date and then check if it's approaching?

Use EDATE to calculate the expiration date, then nest it in an IF statement: =IF(EDATE(B2,12)<TODAY(),"Contract expired","Active"). This combines date math with conditional logic to alert you to upcoming or overdue renewals.

EDATE function

Returns 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.

=EDATE(start_date, months)

Generate a EDATE formula

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

Arguments

How EDATE reads its arguments

Argument	Required	Description
start_date	Required	A required date value or date serial number (like DATE function result) representing the reference point; must be a valid date or a text string that Excel can parse as a date.
months	Required	A required number (positive or negative) representing months to add or subtract; must be numeric; decimals are truncated to the integer portion.

Returns

A date value representing the calculated date.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate when an employee's probation period ends

Name	Hire Date
Emma Davis	2023-11-01

=EDATE(B2,6)

Result: 2024-05-01

Emma was hired on 2023-11-01. EDATE adds exactly 6 months to calculate her probation end date of 2024-05-01. HR can now schedule her evaluation meeting.

2. Find when an employee's annual contract renews

Name	Hire Date
James Williams	2020-09-05

=EDATE(B2,12)

Result: 2021-09-05

James was hired on 2020-09-05. EDATE adds 12 months to determine his one-year contract renewal date of 2021-09-05, when management should review terms and compensation.

3. Determine the 90-day review date for a new hire

Name	Hire Date
Lisa Rodriguez	2023-01-10

=EDATE(B2,3)

Result: 2023-04-10

Lisa started on 2023-01-10. EDATE adds 3 months to find her 90-day checkpoint of 2023-04-10, when her manager will conduct a formal performance assessment and provide feedback.

Common errors

Which EDATE error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The start_date argument contains text that Excel cannot interpret as a valid date, such as =EDATE("hello", 6) or =EDATE("not a date", 12).	Ensure start_date is a valid date value, date serial number, or use DATEVALUE to convert text: =EDATE(DATEVALUE("2023-11-01"), 6).
#VALUE!	The months argument contains text that cannot be converted to a number, such as =EDATE(DATE(2023,11,1), "six") or =EDATE(TODAY(), "abc").	Provide a numeric value for months, or ensure the cell you reference contains a number: =EDATE(B2, 6) instead of =EDATE(B2, "6").
#NUM!	The months argument is a number so large that the resulting date falls outside Excel's valid date range (January 1, 1900 to December 31, 9999), such as =EDATE(TODAY(), 1000000).	Use a months value that keeps the result within Excel's date range; for most real-world scenarios, use values between -600 and 600.

Tips and when to use something else

EDATE preserves the day of the month when possible. If the target month has fewer days, it returns the last day: hiring someone on Jan 31, their one-month check-in is Feb 28 (or Feb 29 in leap years).
Use negative months to calculate past dates: =EDATE(TODAY(), -6) returns the date from six months ago, useful for backtracking contract start dates or eligibility periods.
For counting specific time units between two dates (days, months, years), use DATEDIF instead of EDATE; DATEDIF tells you how much time has passed, while EDATE creates a new date.
Combine EDATE with TODAY() for dynamic calculations that update automatically: =EDATE(TODAY(), 3) always shows the date three months from today, perfect for setting contract expiration reminders.

Related functions

TODAYTODAY returns the current date as a date value, automatically updating each time the spreadsheet opens or recalculates.DATEDATE returns a date serial number from year, month, and day arguments, enabling date construction from separate numeric components.DATEVALUEDATEVALUE converts text strings representing dates into serial numbers Excel and Sheets recognize, enabling date calculations and sorting.DATEDIFReturns the number of time units (days, months, years) between two dates, useful for calculating durations, aging, or time-to-deadline.

Frequently asked questions

Can EDATE handle negative months?: Yes, EDATE subtracts months when you pass a negative value. For example, =EDATE(DATE(2024,6,15), -3) returns March 15, 2024, going backward three months. This is useful for calculating eligibility start dates or lookback periods.
Why does EDATE give Feb 28 instead of Feb 31 when I add months to Jan 31?: EDATE intelligently handles months with fewer days. When the target month cannot accommodate the original day, it returns the last valid day of that month. This prevents errors and ensures dates remain valid year after year.
Can I use EDATE with text dates like "2023-11-01"?: EDATE requires a date value or serial number, not text. If you have text dates, convert them first with DATEVALUE: =EDATE(DATEVALUE("2023-11-01"), 6). Alternatively, use DATE to build a date: =EDATE(DATE(2023,11,1), 6).
How do I calculate a contract expiration date and then check if it's approaching?: Use EDATE to calculate the expiration date, then nest it in an IF statement: =IF(EDATE(B2,12)<TODAY(),"Contract expired","Active"). This combines date math with conditional logic to alert you to upcoming or overdue renewals.

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