Cellwise Try it free

Excel guide · 3 min read

How to find the last day of the month in Excel

Quick answerUse =EOMONTH(A2,0). It returns the last day of the month that A2 falls in, so a date of November 2, 2026 gives November 30, 2026. Change 0 to 1 for the end of next month or -1 for the end of last month. If the result shows as a number like 46356, format the cell as a date.

Sample data

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

A
1Invoice date
2Nov 2, 2026
3Feb 14, 2026
4Feb 10, 2028
5Jan 15, 2026
6Dec 18, 2026

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

EOMONTH: the end of this month

EOMONTH means "end of month". The first argument is any date in the month, the second is how many months to move. 0 means the same month. It handles 30- and 31-day months and February for you.

=EOMONTH(A2,0)

Result: Nov 30, 2026

Nov 30, 2026: the last day of the month that contains November 2.

February and leap years

February is where hand-made formulas break. EOMONTH knows that 2026 is not a leap year and 2028 is. Wrap it in DAY to get the number of days in the month, which is handy for daily averages and prorating a monthly bill.

=DAY(EOMONTH(A4,0))

Result: 29

29: February 2028 is a leap month. For A3 (February 2026) the same formula returns 28.

First day of the month

There is no "start of month" function, but the first day is always the day after the end of the previous month. Move back one month with -1, then add 1.

=EOMONTH(A2,-1)+1

Result: Nov 1, 2026

Nov 1, 2026: one day after October 31.

Without EOMONTH: DATE with day 0

DATE accepts a day of 0 and treats it as the day before the 1st. So day 0 of next month is the last day of this month. This is useful when you build dates from separate year and month columns.

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

Result: Dec 31, 2026

Dec 31, 2026. In December, MONTH+1 is 13, and DATE rolls that into January 2027 correctly before stepping back a day.

Last business day of the month

Payroll and billing often fall on the last weekday. Take the month end and step back 1 day if it is a Saturday or 2 if it is a Sunday. WEEKDAY(...,2) gives 6 for Saturday and 7 for Sunday, so subtracting 5 gives exactly the days to remove.

January 31, 2026 is a Saturday, so the answer moves to Friday the 30th. If you also need to skip public holidays, use =WORKDAY(EOMONTH(A2,0)+1,-1,holidays): it starts from the 1st of next month and steps back one working day, skipping weekends and any dates in your holiday list.

=EOMONTH(A5,0)-MAX(0,WEEKDAY(EOMONTH(A5,0),2)-5)

Result: Jan 30, 2026

Jan 30, 2026: the Friday before the Saturday month end.

Common mistakes

EOMONTH shows 46356 instead of a date

EOMONTH returns a date serial number and the cell may still be formatted as General. Select it and choose Home > Number Format > Short Date.

Hard-coding 30 or 31 days

Adding 30 to the first of the month lands on the 31st in some months and in March in February. Use EOMONTH or the DATE day-0 trick.

Getting the end of the wrong month

The second argument counts months from the date: 0 is this month, 1 next month, -1 last month. Leaving it out is not allowed; EOMONTH needs both arguments.

#VALUE! from a text date

If the date was imported as text that Excel cannot read, EOMONTH fails. Convert the column with Data > Text to Columns > Finish first.

FAQ

What is the formula for the last day of the month in Excel?

=EOMONTH(A2,0) returns the last day of the month containing A2. Format the cell as a date if it shows a number.

How do I find the number of days in a month in Excel?

Use =DAY(EOMONTH(A2,0)). It returns 28, 29, 30 or 31 and handles leap years.

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

Use =EOMONTH(A2,-1)+1, or =DATE(YEAR(A2),MONTH(A2),1).

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

Use =EOMONTH(A2,-1). A date in November returns October 31.

How do I check if a date is the last day of the month?

Compare it with its own month end: =A2=EOMONTH(A2,0) returns TRUE only on the last day. Another way is =DAY(A2+1)=1, which is TRUE when the next day is the 1st.

Functions used

Related guides

Updated