How to Use EOMONTH to Get Last Day of the Month in Excel

When working with dates, you often need to dynamically determine the date and the last day of the week in a month. While you can simply check a standard calendar, for automating some tasks in Excel, you may need to dynamically get the last date of the month, especially in February, and determine the day of the week. In a leap year (366 days), February has 29 days, while in a non-leap year, it has 28 days.



How Many Days Are in February for Any Year and Date?

First, let's look at how to calculate the last day of February for any year to determine whether it's a leap year:

How many days in February.

As shown in the example, this formula can be used for any month to get the last day of the month, not just February. Otherwise, it would look like this:

=DATE(YEAR(A2),3,0)

The DATE function generates a date on the fly using three arguments:

  1. Year – You can specify any number within the range from 1900 to 9999.
  2. Month – Any positive or negative number.
  3. Day – Any positive or negative number.

For example, if the third argument is 0 as the day number, Excel will calculate the last day of the previous month. For a clear example, let's use the number 0 to return February 29, 2000:

=DATE(2000,3,0)

In the next example, instead of entering static values for the first two arguments of the DATE function (year and month), the YEAR function is used to return the year from the source dates. Similarly, the MONTH function returns the month number from the source date. This number is incremented by 1 in the formula. Since the third argument is 0, it automatically returns the ordinal number of the last day in the current month that we are interested in.

What Day of the Week Is the Last Day of the Month?

The image below shows how to calculate the day of the week and the last day of February for any year:

=TEXT(DATE(A2,3,0),"dd.mm.yy")&" - "&TEXT(DATE(A2,3,0),"dddd")
What day of the week.

The formula consists of two parts, separated by the concatenation operator (&" - "&):

  1. In the first part, the TEXT function converts the numeric value returned by the DATE function into a date format. This is specified in the second argument of the function – "dd.mm.yy".
  2. In the second part, the TEXT function determines the day of the week for the source date in its first argument, using the cell format "dddd" specified in the second argument.

Example of Using the EOMONTH Function

In newer versions of Excel, the EOMONTH function provides an alternative solution to the above formula:

EOMONTH.

With the EOMONTH function, you can determine the date for the current, previous, or future month by filling in just two arguments:

  1. Start_date – The initial date or a reference to it.
  2. Months – The month offset from the start date. It can be any positive or negative number. Examples of arguments for the EOMONTH function that return the date February 29, 2000:

=EOMONTH("01/01/2000",1)

If you specify a negative number in the second argument, the function returns the last day of the previous month:

=EOMONTH("01/03/2000",-1)

Of course, if you specify 0 in the second argument, you'll get the last day of the current month:

=EOMONTH("01/02/2000",0)

How to Determine If a Year Is a Leap Year in Excel?

There are several solutions to determine if a year is a leap year in Excel. Here are a few:

Determine leap year.

Download an Example of How to Use EOMONTH in Excel download file

When working with dates, a special complexity arises because every 4 years, the number of days in the year is 366, whereas in normal years it is 365. Therefore, sometimes you have to use the above examples and formulas for various calculations in Excel.