How to use EOMONTH Function

0
349

This article explains the use of Excel EOMONTH function in depth. You will see examples demonstrating how to get the last date of the month, last date before or after Nth month from a specified date, calculate remaining days in a month from a date etc.

EOMONTH Function and Its’ Uses

The Excel EOMONTH function returns the serial number of the last day of a month that is before or after a specified number of months from a starting date. In other words, it simply returns end of the month’s date after adding a specified number of months.

For instance, the following function would return the answer as 30-Apr-18 because the same is last day of date supplied in a function.

=EOMONTH(“10-Apr-18”, 0)

You can also use this function to get the number of days in the particular month from an initial date or find out the last weekday in a month etc.

Syntax:

EOMONTH (start_date, months)

Arguments:

  • Start_date: A valid date representing initial or start date.
  • months: The number of months to offset from the start_date. Positive value returns the date in the future and negative value returns the past date.

Note: Both of the arguments are required by the function.



Examples: – Excel EOMONTH Function

After having the understanding of syntax and arguments of EOMONTH function, it is the time to explore the function with several examples. The following examples help in getting more clarity on this function.

Example 1: Get the last day of the month

Situation 1: Returns the last day of the current month

If you are required to return the last day of the current month, there is no need to refer to any cell or writing date manually to supply start_date. It can be supplied using TODAY function which returns the serial number of current date formatted as a date. Enter the following formula in any cell.

=EOMONTH (TODAY(), 0)

last business day of the month excel

Tip: EOMONTH returns the serial number that represents the date, you can use custom date format to a cell(s) to display as valid excel date.

Situation 2: Returns the last day of the month based on specified initial date

Assuming you have columns with the date in column A and you are required to return the last day of the month for each given date, you can enter the following function in cell B2:

=EOMONTH (A2,0)

get last day of the month



Example 2: How to get last day of the next/previous month

And when you want to find the last day of the next month or previous month, make small changes in the function as follow:

=EOMONTH( E3, 1) – returns the serial number of last day of the month, one month after the start date in cell E3.

=EOMONTH( E3, -1) – returns the serial number of last day of the month, one month before the start date in cell E3.

Excel EOMONTH function

 

Tip: Set the specified number of months to get the last date in future or past.

Example 3: Calculate the remaining days in a month

To calculate the total number of days are left in a month from a date, you simply subtract the start date from the date returned by the EOMONTH function as follow in cell C3:

=EOMONTH (B3, 0) – B3

Calculate Remaining Days in a Month

As the function returns the serial number of the last date, start date provided in cell B3 was subtracted from the last day. Since Excel stores date as the serial number, when a serial number of 11-Jan-18 was subtracted from 31-Jan-18, result 20 is returned in the form of Excel valid date which we converted into a normal number by applying general format.

Tip: You can either use keyboard shortcut CTRL + SHIFT + ~ or use Format Cells dialog box to apply the general format to a cell(s).

Additional Notes:-

  • EOMONTH returns a serial number representing a date. As Excel stores date in sequential serial number where the serial number 1 represents 1/1/1900 and display valid Excel date because of default date format. For more info, visit Microsoft Website.
  • When decimal value supplied to months argument, function only add the integer portion to start_date and ignore the decimal fraction.
  • #VALUE! error is returned if months argument is not supplied as a numerical value.
  • Function returns #NUM! error
    • If the supplied start date is not a valid Excel date
    • Final result after adding the required number of months is not a valid Excel date.

Related Articles:-

MONTH | DATE | YEAR | TODAY | NOW | EDATE

LEAVE A REPLY

Please enter your comment!
Please enter your name here