EDATE and EOMONTH: Add Months and Find Month-End

Excel EOMONTH finds the last day of a month, and EDATE adds or subtracts whole months. Together they handle renewal dates, payment due dates, month-end reports and "first day of next month" questions. This guide shows how each works, the patterns interviewers like to ask about, and the month-length traps that catch people who add days instead.

What do EDATE and EOMONTH do?#

Both move a date by a number of whole months. The difference is where they land:

They agree here only because January 31 is itself a month-end. From Mar 15, 2024, EDATE with 1 month gives Apr 15, and EOMONTH gives Apr 30.

Both return a date serial number, the number Excel uses to store a date. If you see something like 45351, the value is right; apply a date format with Ctrl+Shift+# (Windows) or Control+Shift+# (Mac). How the date then looks, such as 2/29/2024 or 29/02/2024, depends on your regional settings.

How to add months to a date with EDATE#

Use EDATE for anything "a number of months later": renewals, probation periods, payment terms. It handles months of different lengths for you:

=EDATE(B2,C2)

Look at Northwind. February 2024 has no 31st, so EDATE stops at the last day of February. Adding 30 days instead, =B2+30, gives March 1, and the error grows with every month you add.

Two more details:

• Negative months go backwards. =EDATE(B2,-6) is six months earlier.
• Decimals are cut off. =EDATE(B2,1.9) adds 1 month, not 2.

A contract that starts on Mar 15 usually ends the day before its renewal: =EDATE(B2,C2)-1 gives Mar 14 of the next year.

How to use Excel EOMONTH to find the end of the month#

EOMONTH moves a number of months and then jumps to that month's last day. The months argument is required, and 0 means "this month":

=EOMONTH(B2,0)

Finance teams use this for month-end close dates and "due at the end of next month" terms. It's also the cleanest end point for monthly totals: "<="&EOMONTH(F5,0) in SUMIFS covers the whole month without you knowing how many days it has. See how to sum values between two dates.

How to find the first day of the month#

There's no "start of month" function, but EOMONTH gets you there in one step. Find a month-end and add 1 day:

Adding 1 works because dates are numbers that count days. The "first day of next month" formula is a classic interview question, because it's short and works for every month length.

How to count the days in a month#

Wrap EOMONTH in DAY. The last day's number is the number of days in that month:

=DAY(EOMONTH(B2,0))

For any date in February 2024 this gives 29, because 2024 is a leap year. For February 2025 it gives 28, and for April it gives 30. This is handy for daily averages, like =C2/DAY(EOMONTH(B2,0)) for a monthly total spread over its days.

How to find the end of the quarter#

Quarters end in March, June, September and December. This formula works out how many months away the quarter-end is, then lets EOMONTH do the rest:

=EOMONTH(B2,MOD(3-MONTH(B2),3))

For Feb 10, 2024 it gives Mar 31, 2024. For May 6, 2024 it gives Jun 30, 2024. MONTH and the other date-part functions are explained in DATE, YEAR, MONTH and DAY in Excel.

What are the common EDATE and EOMONTH mistakes?#

The month-end trap is worth remembering. If your dates are always month-ends, like payroll or billing dates, use EOMONTH(B2,1) to step forward. EDATE keeps the day number, so if each date is built from the one before, it can drift from the 31st down to the 29th and stay there.

What to do with the dates next#

Once you have renewal or due dates, the next questions are usually about the calendar:

• Does it land on a weekend? Use WEEKDAY. See how to find weekends and day names.
• How many working days until then? Use NETWORKDAYS. See NETWORKDAYS and WORKDAY.
• How long has a customer or employee been with you? See how to calculate age or years of service.

Practise it#

Try the days-in-month exercise on this page, then practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet. You'll find renewal dates, month-ends and next-month starts for real contracts, then check which renewals fall on a weekend.