EOMONTH function in Microsoft Excel

EOMONTH function in Excel

The EOMONTH function in excel returns the last date of a month by giving inputs for the start date and then the months after or before you need the last day of the month.

Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

The EOMONTH Function is categorized under Excel Date/Time functions.

Syntax

=EOMONTH(start_date, months)

The EOMONTH function syntax has the following arguments:

• Start_date    It is a Required argument. Pass inside the date that represents the starting date. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
• Months    Required. The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.

Let us implement the EOMONTH function by learning an example.

In our example we want to create a very useful excel sheet to determine the due-dates of the streaming platforms we have enrolled in.

Now we have the following user data as our head start

We want to determine the due date for each subscription

Applying the below formula and copying it till D7,

=EOMONTH(B2,C2)

Gives the output:

Now observe that the Due-date has returned a serial number which is not in the date format and hence not user understandable.

If you ever get this problem just select all the cell in the Due-date field.

• Right Click and Select Format Cells
• Click on Date and Select the format of date you want to choose.
• Click on OK

After formatting the cells, we get the desired output.

Now we have the due-dates for all platforms for our convenience.

Points to remember:

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
• If start_date is not a valid date, EOMONTH returns the #NUM! error value.
• If start_date plus months yields an invalid date, EOMONTH returns the #NUM! error value.

Some examples of EOMONTH function:

Example Data:

Results:

Formula	Description	Result
=EOMONTH(F2,1)	Date of the last day of the month, one month after the date in F2.	2/28/2011
=EOMONTH(F2,-3)	Date of the last day of the month, three months before the date in F2.	10/31/2010

Hope you find this tutorial to be useful 😊

Keep Learning!