Dates & Time

Excel formula for First or last day of the month

Return the last day (or first day) of the month a date falls in.

Copy this formula

=EOMONTH(A2, 0)

How the formula works

EOMONTH(date, 0) returns the last day of the current month. For the first day, use EOMONTH(date, -1)+1 — the last day of the previous month plus one. The offset shifts months forward or back.

Examples

=EOMONTH(A2, -1)+1→2026-06-01

First day of the month.

=EOMONTH(DATE(2026,2,10), 0)→2026-02-28

Last day of February 2026.

=EOMONTH(DATE(2026,10,7), 0)+1→2026-11-01

First day of the following month.

Other ways to do this

First day (DATE)

=DATE(YEAR(A2), MONTH(A2), 1)

Builds the first day directly.

FAQ

How do I get the last day of next month?

Use EOMONTH(A2, 1).

How do I get the last working day of the month?

Use =WORKDAY(EOMONTH(A2, 0)+1, -1). It steps back one working day from the first of next month.

How do I get the first day of the year?

Use =DATE(YEAR(A2), 1, 1).

Why does EOMONTH show a number instead of a date?

The cell is formatted as General. Apply a Date format to display it as a date.

Related formulas

Add or subtract months from a date Get the quarter from a date

Functions used

EOMONTH DATE

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free