Excel EDATE function

Summary

The Excel EDATE function is a convenient tool for calculating a date that is a specific number of months away from a given start date. It can be used to determine future or past dates by adding or subtracting months. EDATE is particularly useful in financial and planning scenarios, such as forecasting due dates, setting schedules, or calculating maturity dates.
Syntax
				
					=EDATE(start_date, months)
				
			
  • start_date: The starting point date as a valid Excel date.
  • months: The number of months to add (positive value) or subtract (negative value) from the start_date.
Return value
A new date represented as an Excel serial number.

How to use

Use EDATE to shift a given date forward or backward by a specified number of months. The function takes two arguments: the start_date, which is the date from which you begin the calculation, and months, which is the number of months you want to add (for future dates) or subtract (for past dates). EDATE is widely used for creating payment schedules, project timelines, and other date-based calculations.

Examples

Simple EDATE
Project Milestone Planning: To calculate a milestone date 3 months after project start:
				
					=EDATE(A1, 3)
				
			
Assuming the project start date is in A1, this formula computes the date three months later.
EDATE for Subscription Expiry
Calculating Subscription Expiry Date: To find when a 12-month subscription will expire:
				
					=EDATE(subscription_start_date, 12)
				
			
This formula calculates the date 12 months after the start of a subscription.
EDATE for Monthly Reports
Generating Monthly Report Dates: To create a series of monthly report dates starting from a specific date:
				
					=EDATE(B1, ROW()-1)
				
			
Placed in a column, this formula, starting from row 1, creates a series of dates each a month apart from the date in B1.
EDATE for Anniversary Dates
Calculating Annual Anniversary Dates: To find the same day of the month, each year from a start date:
				
					=EDATE(C1, 12 * years)
				
			
Assuming C1 contains a birthdate, this formula calculates the number of days from today until the next occurrence of the day in C1.
EDATE for Adjusting to End of Month
Adjusting Dates to End of Month: To ensure the calculated date always falls on the end of the month:
				
					=EOMONTH(EDATE(D1, 6), 0)
				
			
This formula finds the date six months after the date in D1 and adjusts it to the last day of that month.

Additional Notes

  • EDATE simplifies the process of calculating dates that are a certain number of months away, either in the past or future.
  • While EDATE primarily changes the month component of a date, it also intelligently handles year changes and month-end dates.
  • The start_date must be a valid Excel date; otherwise, EDATE returns a #VALUE! error.
  • For time-inclusive dates (datetime), EDATE removes the time component. Additional calculations are needed to retain time information.

Related Functions

Excel EOMONTH function

The Excel EOMONTH function finds the last day of a month a specified number of months from a start date, crucial for financial deadlines.

Excel WORKDAY function

The Excel WORKDAY function adds workdays to a date, skipping weekends, perfect for project deadlines.

Content Navigation