Excel is often used to work with Dates, a task for which it is well suited. A common request is to use Excel to find the last day of any given month, or date. For this, Excel provides the user with two options: Date function or EOMONTH function.

For example, let’s suppose A2 in the tables below houses the date 17-Jun-2015 and you wish to have Excel reference this cell and return the date of the last day of the month May, June, and July.

## Using the Date Function

To get the date of the last day of the month in Excel using the Date function, use the formula:

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

#### Example

A B C D
1 Current Date Formula Result Notes
2 6/17/2015 =DATE(YEAR(A2),MONTH(A2)+1,0)  6/30/2015 Returns the date for the last day in June
3   =DATE(YEAR(A2),MONTH(A2),0) 5/31/2015 Returns the date for the last day in May
4   =DATE(YEAR(A2),MONTH(A2)+2,0) 7/31/2015 Returns the date for the last day in July
5   =DATE(YEAR(A2),MONTH(A2)-6,0) 11/30/2014 Returns the date for the last day in the month 6 months before the date in A2. (This is not the same result as using the EOMONTH function)

Note: The formula in B2 adds 1 month to the date in A2 (June becomes July) and using zero as the day which forces Excel to return the last day of the month before, i.e. July.

## Using the EOMONTH Function

To get the date of the last day of the month in Excel using the EOMONTH function, use the formula:

=EOMONTH(A2,0)

#### Example

A B C D
1 Current Date Formula Result Notes
2 6/17/2015 =EOMONTH(A2,0)  6/30/2015 Returns the date for the last day in June
3   =EOMONTH(A2,-1)  5/31/2015 Returns the date for the last day in May
4   =EOMONTH(A2,1)  7/31/2015 Returns the date for the last day in July
5   =EOMONTH(A2,-6) 12/31/2014 Returns the date for the last day in the month 6 months before the date in A2

Note: Both the Date function and the EOMONTH function return the serial number of the date. To get the actual date, the user must apply a Date format to cell containing the result.