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.