Knowledge base / Excel

EOMONTH in Excel: month-end dates and reporting boundaries

I explain month offsets and the difference between a calendar month-end, a filtering boundary and the last working day.

Kajo RudzińskiPublished Updated 6 min read

The final tiles of blank month calendars are illuminated as month boundaries.

When I need the date of a month’s final day, I use EOMONTH. I supply a reference date and an offset in months. The Polish function name is NR.SER.OST.DN.MIES. The older Polish version of this entry used the incorrect name NR.KONIEC.MIES; the revised title and formulas correct it while retaining the existing page address.

The examples use synthetic dates and commas between arguments in English formulas. Results follow from Microsoft’s documentation and the calendar, without running a workbook. Regional settings may require another separator. I will start with the month-end date, then distinguish a reporting boundary from the last working day.

Syntax and the meaning of zero

Microsoft documents EOMONTH with two required arguments:

=EOMONTH(start_date,months)

The first argument identifies a reference date. The second moves the selected month forward or backward. A positive offset moves forward; a negative offset moves back. Zero identifies the end of the reference date’s month. That does not necessarily mean the current month: it depends on the input.

The result is a serial number representing a date. Apply a date format to display it as one. If you see a serial number, check formatting before changing the formula. Microsoft’s documentation covers Excel 2016, 2019, 2021, 2024 and Microsoft 365, among other listed editions.

Three results from one input date

Enter =DATE(2024,6,15) in A2. This constructs June 15, 2024 without relying on a piece of date text. Compare offsets 0, 1 and -1:

Formula Result displayed as a date Meaning
=EOMONTH(A2,0) 2024-06-30 End of the input month
=EOMONTH(A2,1) 2024-07-31 End of the next month
=EOMONTH(A2,-1) 2024-05-31 End of the previous month

I first select a month and then its final day. The input may be any day within that month. I do not add thirty days because months have unequal lengths. Moving to the next month is different from adding a fixed number of days to the original date.

For an input in February 2024, that month’s end is February 29. For an input in February 2025, it is February 28. This small calendar comparison demonstrates why entering a fixed month length manually is an unreliable approach. The required result depends on the year as well as the month.

Establish the boundaries of a monthly report

For a monthly report, I often need the start of the selected month and the start of the next one. With the reference date in A2, these are:

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

For June 15, 2024, the first formula gives June 1 and the second gives July 1. Adding one day to May 31 produces the first boundary. Adding one day to June 30 produces the next. They support a period described as at or after the beginning of June and before the beginning of July.

This definition is useful when a source also stores time. Microsoft explains date serials and fractional time values. Comparing complete timestamps with June 30 at midnight can omit events later that day. I establish the input type and the meaning of a boundary instead of assuming that the displayed date describes the whole value. Hiding the time in a display format does not remove it from the underlying data.

This is not a ready-made aggregation formula for every import. You still need to establish that the inputs are valid Excel date values, how time is stored and which records should be included. Two correct boundaries provide part of the solution. Source quality and the business definition of the report remain separate responsibilities.

Calendar month-end and the last working day

A month can end on a weekend. For a Monday-to-Friday working-week model, I can combine EOMONTH with WORKDAY:

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

Start with the first day of the next month and move back one working day. June 2024 ends on Sunday, June 30. The starting point is July 1, and the result is Friday, June 28. This example does not supply a holiday list.

If H2:H4 contains valid dates representing additional non-working days, pass that range as the third argument:

=WORKDAY(EOMONTH(A2,0)+1,-1,H2:H4)

You supply the applicable calendar. The function does not automatically retrieve public holidays or company closures. A different weekend pattern needs a corresponding solution, such as WORKDAY.INTL. Removing Saturdays and Sundays alone should not be described as implementing an organization’s complete working calendar.

A row of date tiles is bounded by two period gates, with the final tile remaining before the next period begins.

Find year-end or check whether a date is month-end

For the date in A2, the end of its calendar year can be expressed as:

=EOMONTH(A2,12-MONTH(A2))

If A2 is in June, MONTH gives 6, making the offset six months. That selects December, so the result is December 31 of the same year. This is a calendar-year calculation. An organization using another reporting year needs its own rule rather than a relabelled December boundary.

For a date-only value without a time component, you can test whether it is the month’s final date:

=A2=EOMONTH(A2,0)

The logical result indicates whether the values are equal. With a timestamp, establish how to handle the time component separately. Displaying only the date does not remove that component. The DAY entry explains extracting the day number instead of retaining the complete date.

Watch the arguments and the question

Microsoft states that a non-integer month offset is truncated. I use whole months explicitly so that the formula does not suggest a shift of half a month. EOMONTH selects a month; it does not interpolate a point between two dates. A clear argument is easier for another person to interpret.

I avoid ambiguous date text and use a cell with a valid date or DATE. If a calculation fails, inspect the argument, supported date range and import source. Error handling should follow an agreed policy for the data rather than merely removing a visible message from the worksheet.

Also check the underlying question. If you need to shift a particular date by months rather than find month-end, investigate a date-shifting function appropriate to that requirement. For weekly reporting, see WEEKNUM and ISOWEEKNUM. A reporting definition should determine the period rather than the most familiar function name.

An exercise with the end of February

Use DATE to enter February 15, 2024 and February 15, 2025. For each, find month-end and the beginning of the following month. The expected pairs are February 29 and March 1, 2024, then February 28 and March 1, 2025. Explain the difference using the month and year.

Next, say whether you need a calendar date, a filtering boundary or a working day. Those are three different requirements, even when an ordinary month produces similar-looking results. I would consider the exercise complete when you can select the appropriate version and explain its limits.

Key takeaways

What is EOMONTH called in Polish Excel?

NR.SER.OST.DN.MIES. The older Polish entry used the incorrect name NR.KONIEC.MIES; the existing page address is preserved.

What does a zero offset mean in EOMONTH?

It selects the end of the month containing the first argument. That is the reference date’s month, which does not have to be the current month.

Is month-end always a working day?

No. For a Monday-to-Friday model, combine EOMONTH with WORKDAY and supply an applicable holiday list. Other weekend patterns need an appropriate calendar.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.