Excel guide · 5 min read

How to get the first day of the month in Excel

Q: How do I get the first day of the current month in Excel?

Use =EOMONTH(TODAY(),-1)+1. It updates every day and always shows the 1st of the month you are in.

Q: How do I get the first day of next month in Excel?

Use =EOMONTH(A2,0)+1, or =DATE(YEAR(A2),MONTH(A2)+1,1). Both handle December by rolling into January.

Q: How do I get the first day of the quarter?

Use =DATE(YEAR(A2),INT((MONTH(A2)-1)/3)*3+1,1). For a November date it returns October 1, the start of the fourth quarter. For the first day of the year, use =DATE(YEAR(A2),1,1).

Quick answerUse =EOMONTH(A2,-1)+1: it finds the last day of the previous month and adds one day, so November 17, 2026 gives November 1, 2026. =DATE(YEAR(A2),MONTH(A2),1) gives the same result. For the first day of next month use =EOMONTH(A2,0)+1. Format the cell as a date if you see a number such as 46327.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	E
1	Order date	Year	Month	Holidays
2	Nov 17, 2026	2026	11	Jan 1, 2026
3	Feb 14, 2026	2027	2	Nov 26, 2026
4	Dec 9, 2026	2026	12	Dec 25, 2026
5	Mar 12, 2026			Jan 1, 2027
6	Jan 20, 2027
7	Aug 25, 2026 14:30

Download this sheet as an Excel practice file (.xlsx)

EOMONTH(A2,-1)+1: the first of this month

Excel has no "start of month" function, but it has EOMONTH, end of month. EOMONTH(A2,-1) is the last day of the month before A2, and the next day is always the 1st. The sheet holds order dates; A2 is November 17, 2026.

EOMONTH returns a plain date serial number. If the cell shows 46327, it is still formatted as General: choose Home > Number Format > Short Date.

=EOMONTH(A2,-1)+1

Result: Nov 1, 2026

Nov 1, 2026.

DATE(YEAR(A2),MONTH(A2),1): the readable way

This builds a new date from A2's year and month with the day set to 1. It reads exactly like what it does, which helps when someone else opens the file. It is also the natural formula when the year and month sit in separate columns: =DATE(B3,C3,1) returns Feb 1, 2027.

A third way you will see online is =A2-DAY(A2)+1, which subtracts the day number. It works for plain dates, but see the time-of-day gotcha below.

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

Result: Nov 1, 2026

Nov 1, 2026, the same as EOMONTH.

First day of next month or last month

Change EOMONTH's second argument to move by months. EOMONTH(A2,0)+1 is the day after this month ends, the first of next month. EOMONTH(A2,-2)+1 is the first of last month, Oct 1, 2026. Year ends are handled for you.

With DATE, add 1 to the month: =DATE(YEAR(A4),MONTH(A4)+1,1). A4 is December 9, 2026, so the month becomes 13, and DATE rolls that into January 2027 correctly. You do not need an IF for December.

=EOMONTH(A2,0)+1

Result: Dec 1, 2026

Dec 1, 2026, the first day of the month after November.

First Monday of the month

Start from the 1st and add the days until the next Monday. WEEKDAY(date,2) numbers the days Monday 1 to Sunday 7, and MOD(1-WEEKDAY(...,2),7) is how many days to move forward: 0 if the 1st is already a Monday. December 1, 2026 is a Tuesday, so the first Monday is six days later.

For another weekday, change the 1 at the start of MOD to that day's number: 2 for Tuesday up to 5 for Friday. For the second Monday, add 7 to the result. November 1, 2026 is a Sunday, so for A2 the same formula returns Nov 2.

=DATE(YEAR(A4),MONTH(A4),1)+MOD(1-WEEKDAY(DATE(YEAR(A4),MONTH(A4),1),2),7)

Result: Dec 7, 2026

Dec 7, 2026, the first Monday of December.

First business day of the month

For many reports and payment runs the date you want is the first weekday, not the 1st. WORKDAY does this in one step: =WORKDAY(EOMONTH(A2,-1),1,E2:E5) starts from the last day of the previous month and moves forward one working day, skipping Saturdays, Sundays and any date in the holiday list E2:E5. WORKDAY is in every current version of Excel. If your weekend is not Saturday and Sunday, WORKDAY.INTL takes a weekend argument.

Without a holiday list, you can push a Saturday or Sunday to Monday with WEEKDAY: if the 1st is day 6 or 7, add 8 minus the weekday number. November 1, 2026 is a Sunday, so this moves it to Monday the 2nd. This version does not know about holidays: for January 2027 it returns Friday January 1, which is New Year's Day. Use WORKDAY with a holiday list when that matters.

=EOMONTH(A2,-1)+1+IF(WEEKDAY(EOMONTH(A2,-1)+1,2)>5,8-WEEKDAY(EOMONTH(A2,-1)+1,2),0)

Result: Nov 2, 2026

Nov 2, 2026: the 1st is a Sunday, so the first business day is Monday.

The gotcha: dates with a time of day

A7 holds August 25, 2026 at 14:30, the kind of value you get from an order system export. =A7-DAY(A7)+1 subtracts whole days and keeps the time, so the result is August 1 at 14:30. It looks right when the cell shows only the date, but it fails exact matches: a lookup or COUNTIFS for "August 1" will not find it, and grouping by month start splits into extra rows.

EOMONTH and DATE drop the time, so both return midnight on August 1. If you must use the subtraction, wrap the date in INT first: =INT(A7)-DAY(A7)+1.

=TEXT(EOMONTH(A7,-1)+1,"yyyy-mm-dd hh:mm")

Result: 2026-08-01 00:00

2026-08-01 00:00. The same TEXT check on =A7-DAY(A7)+1 shows 2026-08-01 14:30.

Which formula to pick

For the first of this, next or last month: EOMONTH(date,n)+1, with n one less than the month you want (-1 for this month, 0 for next). For a date built from separate year and month cells, or when readability matters most: DATE(year,month,1). For the first business day: WORKDAY with a holiday list.

A month-start column is also the cleanest way to group by month. Add =EOMONTH(A2,-1)+1 next to each order date, then use it in SUMIFS, COUNTIFS or a PivotTable row. Every date in the same month gets the same value, and the dates still sort in time order, which month names do not.

Common mistakes

Using EOMONTH(A2,0)+1 for this month

That is the first day of next month. For the first of the current month, go back one month: EOMONTH(A2,-1)+1.

Seeing a number like 46327

The result is a date serial number in a General cell. Format it with Home > Number Format > Short Date.

Text dates

If A2 is text that Excel does not read as a date, EOMONTH returns #VALUE! and DATE(YEAR(),...) does too. Convert the column first with Data > Text to Columns > Finish, or DATEVALUE.

Keeping the time with A2-DAY(A2)+1

Use EOMONTH or DATE, which return midnight, or wrap the date in INT.

FAQ

How do I get the first day of the current month in Excel?

Use =EOMONTH(TODAY(),-1)+1. It updates every day and always shows the 1st of the month you are in.

How do I get the first day of next month in Excel?

Use =EOMONTH(A2,0)+1, or =DATE(YEAR(A2),MONTH(A2)+1,1). Both handle December by rolling into January.

How do I get the first day of the quarter?

Use =DATE(YEAR(A2),INT((MONTH(A2)-1)/3)*3+1,1). For a November date it returns October 1, the start of the fourth quarter. For the first day of the year, use =DATE(YEAR(A2),1,1).

How do I show the first day of the month as a month name?

Keep the real date and apply a custom format such as mmm yyyy, so the cell shows Nov 2026 but still sorts and calculates as a date. =TEXT(EOMONTH(A2,-1)+1,"mmm yyyy") gives the same label as text.

Functions used

Related guides

Updated November 3, 2026