For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Date and time

DATE Function

Returns the date serial number of a specified date


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Syntax

DATE(year, month, day)

  • year  Required. This value can contain 1 to 4 digits. If the number has the value...
    • 0 through 1899...Excel adds 1900 to the value. Example: DATE(100,1,1) =
      DATE(100+1900,1,1) = the date serial number for DATE(2000,1,1)
    • 1900 through 9999...Excel uses that value for the year.
    • Less than zero or greater than 9999...DATE returns the #NUM error value.
  • Recommendation: Specify years using only 1900 and above. Using a lower value offers no benefit and adds confusion.
     
  • month  Required. A positive or negative number that represents the number of the month, where 1 equals January and 12 equals December. If the number is...
    • greater than 12...adds the appropriate number of years and months to the year. Example: DATE(2001,26,1) = DATE(2001, 24 + 2, 1) = February 1, 2003.
    • less than 1...subtracts the month value plus 1 from month 1 of the year specified. Examples:
      • DATE(2001, 1, 1) = January 1, 2001
      • DATE(2001, 0, 1) = December 1, 2000
      • DATE(2001, -1, 1) = November 1, 2000
      • DATE(2001, -2, 1) = October 1, 2000
      • DATE(2001, -14, 1) = October 1, 1999

  • day  Required. A positive or negative number, representing the day of the month. If the number is...
    • greater than the number of days in the specified month...adds that number of days to the first day of the month. Examples:
      • DATE(2001, 1, 31) = January 31, 2001
      • DATE(2001, 1, 32) = February 1, 2001
      • DATE(2001, 1, 33) = February 2, 2001
    • less than one...returns the number of days, plus one, from the first day of the month. Examples:
      • DATE(2004, 3, 1) = March 1, 2004
      • DATE(2004, 3, 0) = February 29, 2004, the last day of the previous month
      • DATE(2004, 3, -1) = February 28, 2004, two days prior to March 1, 2004
      • DATE(2004, 3, -1000) = June 4, 2001, 1001 days prior to March 1, 2004
  • Tip: To return the last day of any month use DATE(year, month +1, 0), as illustrated for February 29, 2004 above.

Remarks

When you add or subtract months to a date near the end of a month, DATE can produce unexpected results. Examples for the EDATE function illustrate these issues and show EDATE's alternate results.

Applies To

Excel 2003 and above

Examples

You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

Examples for Excel's DATE function

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards