DATE FORMULAS

Understanding some of the simple Excel date functions will make your day-to-day use of Excel more efficient. Instead of typing a list of dates into your spreadsheet, try using one of the Excel date functions to calculate the dates in your Excel spreadsheet for you. Shown below are three examples of Excel date functions: TODAY, EDATE, and EOMONTH.

TODAY Function

= TODAY ()

Returns the current date formatted as a date.

EDATE Function

= EDATE ( Start_date , Months)

The EDATE function returns the serial number of the date that is the indicated number of months before or after the start date. The start_date is the beginning, or reference date, that you start with. This can be entered directly into the formula or be a cell reference or formula. Months is the number of months before or after the start_date that you would like to return. Use positive integers to move forward from the start_date and use negative integers to move backwards from the start_date.

Note that the definition of the formula states that the serial number will be returned. This means that you will need to format the result in order to display the result as a date. For more information on how dates are stored in Excel please visit the section: using dates in Excel

 EDATE Examples  EDATE Formula Results
 = EDATE ( “12/14/2013” , -1 )  11/14/2013
 = EDATE ( “12/14/2013” , 1 )  1/14/2014
 = EDATE ( Today() , 2 )  Two months from current day

 EOMONTH Function

= EOMONTH (Start_date , Months )

The EDATE function returns the serial number of the last day of the month before or after a specified number of months. The start_date is the beginning, or reference date, that you start with. This can be entered directly into the formula or be a cell reference or formula. Months is the number of months before or after the start_date that you would like to return. Use positive integers to move forward from the start_date and use negative integers to move backwards from the start_date.

Note that the definition of the formula states that the serial number will be returned. This means that you will need to format the result in order to display the result as a date. For more information on how dates are stored in Excel please visit the section: using dates in Excel

 EOMONTH Examples EOMONTH Formula Results
 = EOMONTH ( “12/14/2013” , 0)  12/31/2013
 = EOMONTH ( “12/14/2013” , 1)  1/31/2014
 = EOMONTH ( “12/14/2013” , -1  11/30/2013
 = EOMONTH ( “12/14/2013” , 0) + 1  12/1/2013
 = EOMONTH ( Today() , 0)  Last day of the current  month
 = EOMONTH ( Today() , -1) + 1  First day of current month