Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

EOMONTH and EDATE Equivalents in pandas

Reproduce Excel's EDATE and EOMONTH in pandas for whole columns — month arithmetic that clamps 31 January to 28 February, month ends that do not depend on whether a date is already a month end, a different number of months per row, first-of-month dates and the time-of-day that Excel drops.
Excel functions in pandasDates & timespandas

Contract end dates, invoice due dates, reporting periods and subscription renewals all rest on two Excel functions. EDATE(start, months) moves a date by whole months, and EOMONTH(start, months) returns the last day of the month that many months away. pandas has the pieces to do the same for a whole column at once, but its most obvious tool, MonthEnd, behaves differently from EOMONTH in a way that quietly shifts some dates by a month. This guide, part of Excel Formula Equivalents in pandas, gives exact equivalents, checked against a spreadsheet, and covers the edge cases that matter in schedules.

EDATE and EOMONTH from 15 January and 31 January From 15 January, EDATE one month gives 15 February and EOMONTH one month gives 28 February. From 31 January, EDATE one month also gives 28 February because the day is clamped to the shorter month, and EOMONTH one month again gives 28 February. 15 Jan start date 31 Jan start date 15 Feb EDATE(15 Jan, 1) 28 Feb EDATE(31 Jan, 1), EOMONTH(either, 1)

Prerequisites

Bash
pip install pandas numpy openpyxl

Dates that exercise the edge cases: a 31st, a mid-month date, a leap day, a date with a time and a year end:

Python
import numpy as np
import pandas as pd

contracts = pd.DataFrame({
    "contract": ["C1", "C2", "C3", "C4", "C5"],
    "start": pd.to_datetime(["2026-01-31", "2026-01-15", "2024-02-29",
                             "2026-03-31 14:30", "2026-12-31"], format="mixed"),
    "term_months": [1, 12, -12, 2, 3],
})
contracts.to_excel("contracts.xlsx", index=False)
df = pd.read_excel("contracts.xlsx")

Step 1 — EDATE with DateOffset

For a fixed number of months, add a DateOffset. It moves the month and clamps the day to the end of shorter months, exactly as EDATE does:

Python
plus_one = (df["start"] + pd.DateOffset(months=1)).dt.normalize()
print(plus_one.dt.strftime("%Y-%m-%d").tolist())
# ['2026-02-28', '2026-02-15', '2024-03-29', '2026-04-30', '2027-01-31']

Recalculating =EDATE(DATE(2026,1,31),1) in a spreadsheet gives 28 February 2026, and =EDATE(DATE(2024,2,29),-12) gives 28 February 2023 — the same clamping. The normalize() matters for C4: DateOffset keeps the 14:30 time, but EDATE returns a whole date and drops it. Leave the time in and a later comparison such as "due date is before today" can flip on the due day itself.

Step 2 — EDATE with a different number of months per row

In the workbook, the months argument usually points at a cell — each contract has its own term. DateOffset takes a single number, so for a column of month counts, use monthly periods: shift the month, then put back the original day, clamped to the length of the target month:

Python
def edate(dates, months):
    dates = pd.to_datetime(dates)
    target = dates.dt.to_period("M") + np.asarray(months, dtype=int)
    day = np.minimum(dates.dt.day, target.dt.days_in_month)
    return target.dt.to_timestamp() + pd.to_timedelta(day - 1, unit="D")

df["end"] = edate(df["start"], df["term_months"])
print(df["end"].dt.strftime("%Y-%m-%d").tolist())
# ['2026-02-28', '2027-01-15', '2023-02-28', '2026-05-31', '2027-03-31']

Everything happens on whole columns, so this is fast for hundreds of thousands of rows, and to_timestamp() produces midnight, dropping the time like EDATE. Excel truncates fractional months — EDATE(date, 2.9) moves two months — and converting the months to integers with np.asarray(months, dtype=int) does the same. Negative fractions truncate towards zero in both.

How the per-row edate function works Each date becomes a monthly period, the row's number of months is added, the original day number is clamped to the number of days in the target month, and the result is converted back to a date at midnight. To month period 2026-01 Add months per row Clamp the day min(day, days_in_month) Back to a date midnight

Step 3 — EOMONTH with periods, not MonthEnd

The obvious pandas tool for month ends is the MonthEnd offset. It is also the trap:

Python
from pandas.tseries.offsets import MonthEnd

print((df["start"] + MonthEnd(1)).dt.strftime("%Y-%m-%d").tolist()[:2])
# ['2026-02-28', '2026-01-31']

MonthEnd(1) means "roll forward to the next month end". For 15 January that is 31 January — the current month — while for 31 January, already a month end, it is 28 February. EOMONTH(date, 1) returns the end of the following month for both. The period approach has no such dependency:

Python
def eomonth(dates, months=0):
    dates = pd.to_datetime(dates)
    target = dates.dt.to_period("M") + np.asarray(months, dtype=int)
    return target.dt.to_timestamp(how="end").dt.normalize()

print(eomonth(df["start"], 1).dt.strftime("%Y-%m-%d").tolist())
# ['2026-02-28', '2026-02-28', '2024-03-31', '2026-04-30', '2027-01-31']
print(eomonth(df["start"], 0).dt.strftime("%Y-%m-%d").tolist()[:2])
# ['2026-01-31', '2026-01-31']

to_timestamp(how="end") returns the last nanosecond of the month; normalize() brings it back to midnight so it compares equal to a plain date. The months argument accepts one number or a column, just like the Excel function referencing a cell.

MonthEnd(1) versus EOMONTH(date, 1) MonthEnd(1) rolls forward to the next month end, so 15 January becomes 31 January while 31 January becomes 28 February. EOMONTH with one month and the period-based function return 28 February for both dates. date + MonthEnd(1) 15 Jan → 31 Jan 31 Jan → 28 Feb result depends on the start day eomonth(date, 1) 15 Jan → 28 Feb 31 Jan → 28 Feb same as Excel for every date

Step 4 — First day of the month

The Excel idiom =EOMONTH(date, -1) + 1 gives the first day of the date's month. With periods it is one call:

Python
first = df["start"].dt.to_period("M").dt.to_timestamp()
print(first.dt.strftime("%Y-%m-%d").tolist())
# ['2026-01-01', '2026-01-01', '2024-02-01', '2026-03-01', '2026-12-01']
same_as_excel = eomonth(df["start"], -1) + pd.Timedelta(days=1)
print((first == same_as_excel).all())       # True

First-of-month dates are the usual key for monthly aggregation; grouping by them sorts naturally and writes to Excel as real dates. Monthly and fiscal grouping more broadly is covered in Group Excel Data by Fiscal Year and ISO Week.

Step 5 — Build month-end schedules without drift

A payment schedule built by applying EDATE repeatedly — each date one month after the previous — drifts. Start on 31 January and the second date is 28 February; one month after that is 28 March, not 31 March, and every later date sticks to the 28th:

Python
chained = [pd.Timestamp("2026-01-31")]
for _ in range(3):
    chained.append(chained[-1] + pd.DateOffset(months=1))
from_start = [pd.Timestamp("2026-01-31") + pd.DateOffset(months=k) for k in range(4)]
month_ends = eomonth(pd.Series([pd.Timestamp("2026-01-31")] * 4), range(4))
print([d.strftime("%m-%d") for d in chained])       # ['01-31', '02-28', '03-28', '04-28']
print([d.strftime("%m-%d") for d in from_start])    # ['01-31', '02-28', '03-31', '04-30']
print(month_ends.dt.strftime("%m-%d").tolist())     # ['01-31', '02-28', '03-31', '04-30']

Always offset from the original start date — EDATE(start, k) for the k-th payment — rather than from the previous payment. When the schedule is meant to fall on month ends, use EOMONTH semantics directly; it states the intent and cannot drift.

Step 6 — Prorate by days in the month

A common reason for EOMONTH in a workbook is proration: a subscription that starts on the 20th is charged for the remaining days of that month. Excel writes DAY(EOMONTH(start, 0)) for the number of days in the month and EOMONTH(start, 0) - start + 1 for the days remaining. Both translate directly:

Python
month_end = eomonth(df["start"], 0)
days_in_month = df["start"].dt.days_in_month
days_left = (month_end - df["start"].dt.normalize()).dt.days + 1
monthly_fee = 90.0
df["first_charge"] = (monthly_fee * days_left / days_in_month).round(2)
print(pd.DataFrame({"start": df["start"].dt.strftime("%m-%d"), "left": days_left,
                    "of": days_in_month, "charge": df["first_charge"]}).values.tolist()[:3])
# [['01-31', 1, 31, 2.9], ['01-15', 17, 31, 49.35], ['02-29', 1, 29, 3.1]]

dt.days_in_month handles leap years, so 29 February 2024 is correctly the last of 29 days. Normalising the start date before subtracting keeps a start time such as 14:30 from shortening the count by a day. If the charge must match the workbook to the cent, round with the Excel-compatible function from Match Excel Rounding in pandas rather than round.

Step 7 — Write the dates back as Excel dates

The results are ordinary datetime columns, so to_excel writes them as real Excel dates. Give them a date format so they do not show a time of 00:00:00:

Python
with pd.ExcelWriter("contracts_out.xlsx", engine="openpyxl", datetime_format="yyyy-mm-dd") as writer:
    df.assign(month_end=eomonth(df["end"])).to_excel(writer, index=False)
print(pd.read_excel("contracts_out.xlsx")["month_end"].dt.strftime("%Y-%m-%d").tolist()[:2])
# ['2026-02-28', '2027-01-31']

If the workbook will keep calculating after you hand it over — users changing start dates, for example — write the formulas instead of the values, so Excel stays the source of the calculation; see Write a Formula to an Excel Cell with openpyxl.

Common pitfalls

  • Adding 30 days for "one month". It lands on the wrong day in every month that is not 30 days long, and the error grows with every month added. Use month arithmetic.
  • Dates read as text. Month arithmetic needs datetime columns. Convert with pd.to_datetime and an explicit format first.
  • Business days. EDATE and EOMONTH ignore weekends; a month end can be a Sunday. If a due date must fall on a working day, roll it with a business-day offset afterwards, the equivalent of Excel's WORKDAY.
  • Timezone-aware timestamps. Periods drop the timezone. Convert to local dates first when month boundaries depend on the local calendar.

Conclusion

EDATE is DateOffset(months=n) for a fixed count, or a period shift with a clamped day for a count per row; normalise either result to drop the time as Excel does. EOMONTH is a period shift converted back with how="end" and normalised — not MonthEnd, which depends on whether the start is already a month end. Offset every schedule date from the original start to avoid drift, and write the results as formatted Excel dates.

Frequently asked questions

What is the pandas equivalent of EDATE? Add pd.DateOffset(months=n) to a datetime column. Like EDATE, it clamps to the last valid day, so 31 January plus one month is 28 February. Normalise the result to drop the time, which EDATE also discards.

What is the pandas equivalent of EOMONTH? Convert to a monthly Period, add the number of months, and convert back with to_timestamp(how="end") followed by normalize(). That gives the last day of the target month for every date, whether or not it is already a month end.

Why does MonthEnd(1) give different results for different dates? MonthEnd(1) rolls forward to the next month end, so 15 January goes to 31 January but 31 January goes to 28 February. EOMONTH(date, 1) always returns the end of the following month. Use Period arithmetic to avoid the difference.

How do I get the first day of the month? Convert to a monthly Period and back with to_timestamp(), which returns the first day of the month at midnight. It gives the same result as the Excel idiom EOMONTH(date, -1) + 1.

More on excel functions in pandas

More on excel functions in pandas →