Building Dates With DATE and EDATE

In the last lesson you took dates apart. Now you will build them up. Sometimes you have a year, a month, and a day as separate numbers and need to combine them into one real date.

The DATE function does exactly that. And when you need to move a date forward or backward by whole months, EDATE handles the tricky month-length math for you.

These are the go-to tools for scheduling, due dates, and building dates from imported pieces.

The DATE function takes three arguments in a fixed order: year, month, day.

So =DATE(2026, 6, 18) produces a real date value for June 18, 2026. The spreadsheet then displays it using your date format.

This matters when data arrives in pieces. If a year sits in A2, a month in B2, and a day in C2, you can assemble them into one date.

The real value of DATE appears when the parts live in different cells. Suppose A2 holds the year, B2 the month, and C2 the day.

The formula combines them into one proper date you can then sort, subtract, or format. This is common after importing data that split the date into columns.

A neat feature: DATE normalizes out-of-range values. If you give it a month of 13, it rolls into the next year. If you give a day of 0, it returns the last day of the previous month.

For example, =DATE(2026, 1, 0) returns December 31, 2025. And =DATE(2026, 13, 1) returns January 1, 2027.

You can use this on purpose to compute the last day of a month, as you will see soon.

That overflow trick gives a handy pattern: to get the last day of a month, ask for day 0 of the next month.

If A2 holds any date, =DATE(YEAR(A2), MONTH(A2)+1, 0) returns the final day of A2's month, correctly handling 28, 29, 30, or 31 day months.

There is also a dedicated function called EOMONTH that does the same thing more directly, which we will meet next.

The EDATE function shifts a date by a whole number of months. It takes two arguments: a start date and the number of months to move.

A positive number moves forward; a negative number moves back. So =EDATE(A2, 3) returns the date three months after A2, and =EDATE(A2, -1) returns one month earlier.

It keeps the same day-of-month when possible, which is exactly what you want for monthly billing.

You might think adding 30 moves a date by a month, but months have different lengths. Adding 30 days to January 31 lands you in early March, not February.

EDATE understands real calendar months. From January 31, =EDATE(A2, 1) returns February 28 (or 29 in a leap year) because February has no 31st.

That is why EDATE is the correct tool for monthly schedules.

The companion to EDATE is EOMONTH, which returns the end of month a given number of months away.

=EOMONTH(A2, 0) gives the last day of A2's own month. =EOMONTH(A2, 1) gives the last day of next month.

This is the clean way to find statement closing dates or month-end report cutoffs.

A customer subscribes on the date in A2 for a 12-month plan. You want the renewal date.

Use EDATE to jump exactly one year ahead while respecting calendar months. If they signed up on February 29 of a leap year, EDATE gracefully lands on February 28 the following year.

This single formula handles every edge case that manual date math would get wrong.

Combine EOMONTH with simple addition for another classic need: the first day of next month.

EOMONTH gives the last day of the current month, so adding 1 day lands you on the first of the following month.

So =EOMONTH(A2, 0)+1 always returns the first day of the next month, no matter how long the current month is. Tools like these make scheduling formulas rock solid.

Combine EDATE with a starting date to roll out a whole schedule. Put a start date in A2, then in the next cell add one month, and the next, and so on.

For example, =EDATE($A$2, 1), =EDATE($A$2, 2), =EDATE($A$2, 3) lists the same calendar day across consecutive months. The $ signs lock the start cell so you can fill the formula down.

This builds clean monthly billing or payment dates from a single anchor.

Test your date-building skills.

You learned to assemble and shift dates:

• DATE(year, month, day) builds a real date from three numbers, and even normalizes overflow values.
• DATE(YEAR(d), MONTH(d)+1, 0) finds the last day of a month using the day-zero trick.
• EDATE(date, months) shifts by whole calendar months, correctly handling different month lengths.
• EOMONTH(date, months) returns month-end dates, and adding 1 gives the first of the next month.

Next you will measure the gap between two dates.