First and Last Day of the Month in Excel

To get the first day of the month in Excel, use =EOMONTH(A2,-1)+1: it finds the last day of the previous month and adds one day. For the last day of the month, use =EOMONTH(A2,0). This guide is for anyone setting up billing periods, monthly reports or month-to-date totals, and it also covers working days, the first Monday and a list of month starts.

Quick reference: month boundary formulas#

Every formula below uses a date in A2. For February 17, 2025:

EOMONTH and WORKDAY return serial numbers, so format the results as dates (Home > Number Format > Short Date, or Ctrl+Shift+# on Windows).

How to get the first day of the month in Excel#

There are three common formulas, and they all return the same date.

Method 1: EOMONTH plus one day#

=EOMONTH(A2,-1)+1

EOMONTH means "end of month". EOMONTH(A2,-1) moves back one month and returns that month's last day, January 31. Adding 1 gives February 1. This is the version most finance teams use, because the same function also gives the last day.

Method 2: DATE with YEAR and MONTH#

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

This rebuilds the date from its parts with the day set to 1. It is easy to read, and you practise exactly this in the date lessons. Our guide to DATE, YEAR, MONTH and DAY explains each function.

Method 3: subtract the day number#

=A2-DAY(A2)+1

DAY(A2) is 17, so A2 minus 17 is the last day of January, and plus 1 is February 1. One difference: if A2 holds a time as well as a date, this method keeps the time, while the other two return midnight.

How to get the last day of the month#

Use EOMONTH with 0 months:

=EOMONTH(A2,0)

The second argument says how many months to move before jumping to the month-end: 0 is this month, 1 is next month and -1 is last month. EOMONTH knows that February has 28 days in 2025 and 29 in 2024, so you never have to.

The DATE version uses day 0 of next month, which Excel reads as the last day of this month:

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

Both return February 28, 2025. For more on EOMONTH and its partner EDATE, see the EDATE and EOMONTH guide.

How to get the first or last working day of the month#

Wrap the month boundary in WORKDAY, which skips Saturdays and Sundays.

=WORKDAY(EOMONTH(A2,-1),1)

This starts from the last day of the previous month and steps forward one working day. February 1, 2025 is a Saturday, so the first working day is Monday, February 3.

For the last working day, start from the first day of next month and step back one working day:

=WORKDAY(EOMONTH(A2,0)+1,-1)

February 28, 2025 is a Friday, so the result is February 28. Add a holiday range as WORKDAY's third argument to skip public holidays too, for example =WORKDAY(EOMONTH(A2,-1),1,$H$2:$H$10).

How to find the first Monday of the month#

Find the first day of the month, then add the days needed to reach a Monday. With the first day in B2:

=B2+MOD(9-WEEKDAY(B2),7)

WEEKDAY returns 1 for Sunday, 2 for Monday and so on up to 7 for Saturday. MOD(9-WEEKDAY(B2),7) is the number of days from B2 to the next Monday, or 0 if B2 is already a Monday. For September 1, 2025, which is a Monday, the result is September 1. Change the 9 to 10 for Tuesday, 11 for Wednesday, and so on. Our WEEKDAY function guide explains the return types.

How to list the first day of every month#

In Excel 2021, Excel 2024 and Microsoft 365, combine EDATE with SEQUENCE to spill twelve month starts at once:

=EDATE(DATE(2025,1,1),SEQUENCE(12,1,0))

SEQUENCE(12,1,0) produces 0 to 11, and EDATE adds each number of months to January 1, 2025. Format the spill as dates. In older Excel, type the first date and use =EDATE(A2,1) in the next row, then fill down. To shift dates by any number of days, months or years, see adding days, months or years to a date.

Using month boundaries in totals#

Month starts and ends make month-to-date and monthly totals easy. To add up sales in D2:D100 from the first of the month to the date in F1:

=SUMIFS(D2:D100,A2:A100,">="&EOMONTH(F1,-1)+1,A2:A100,"<="&F1)

The criteria join the comparison sign to a date with &. The same pattern with "<="&EOMONTH(F1,0) gives the full month. For quarterly reports, our guide to getting the quarter from a date shows the quarter-start and quarter-end versions.

Common mistakes#

These cause most month-boundary problems:

• A number instead of a date. EOMONTH returns a serial number. Apply a date format.
• Using 0 when you meant -1. EOMONTH(A2,0)+1 is the first of next month; EOMONTH(A2,-1)+1 is the first of this month.
• Hard-coding 30 or 31. =A2+30 doesn't reach the month-end reliably. Use EOMONTH.
• Text dates. EOMONTH returns #VALUE! on text it can't read as a date.
• Dates with times in criteria. A timestamp later on the last day is greater than the plain date, so use "<"&EOMONTH(F1,0)+1 to include the whole last day.

Practise it#

Try the billing period exercise on this page, then open the EDATE, EOMONTH and WEEKDAY lesson, where you find month-ends, the first day of next month and weekend renewals in a live spreadsheet.