DATE Function In Excel

How to utilize the DATE Function in MS Excel (WS)

The syntax and examples in this Excel lesson demonstrate how to utilize the DATE function.

Description

The serial date value for a given date is returned by the DATE function in Microsoft Excel.

The Excel built-in DATE function is within the Date/Time Function category. In Excel, it may be utilized as a worksheet function (WS). The DATE function may be used as part of a formula in a worksheet cell as a worksheet function.

If you’re looking for the DATE function in VBA, please see our DATE function (VBA) page because it has a significantly different syntax.

 

 

When you insert the DATE function into a cell that is structured as General, Excel will format the output as mm/d/yyyy (column E) according on your regional settings. After inputting the formula, you must change the cell’s format to General if you want to view the serial number result (column D) from the DATE function.

 

Syntax

Microsoft Excel’s DATE function has the following syntax:

DATE( year, month, day )

Arguments or Parameters

year —>The year is represented by a number with one to four digits.
month —> a numerical expression of the month’s worth. If the month value is higher than 12, the year value will increase by one year every twelve months. As a result, DATE(2016,13,4) = DATE(2017,1,4), DATE(2016,25,4) = DATE(2018,1,4), and so on.
day —>a number that represents the worth of a day. The appropriate number of months will be added to the month value if the day value exceeds the specified number of days in the month.

Returns

A serial date value is the result of the DATE function. Excel internally maintains dates as serial dates, which indicate the number of days from January 1, 1900.
The DATE function will produce the #NUM! error if the year is above 999.

Note

• The year is determined by adding the year value to 1900 if it falls between 0 and 1899.
• The DATE function takes the year value as the year if the year is within the range of 1900 and 9999.

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000 are all affected.

Function Type

Example of a worksheet function (WS) (as Worksheet Function)

Let’s investigate how to utilize the DATE function as a worksheet function in Microsoft Excel by looking at several instances of the Excel DATE function:

Due to the fact that the DATE function produces a serial date, we decided to display the result in column D as an unformatted serial date.

As this is how a user would style the results, we also intended to provide the result to you as a structured date. We have structured the DATE function’s output as mm/d/yyyy in column E.

The following DATE samples might return based on the Excel spreadsheet mentioned above:

 

=DATE(A3,B3,C3)
Result: 42613  'Which can be formatted as "8/31/2016"

=DATE(A4,B4,C4)
Result: 42551  'Which can be formatted as "6/30/2016"

=DATE(A5,B5,C5)
Result: 42373  'Which can be formatted as "1/4/2016"

=DATE(A6,B6,C6)
Result: 42739  'Which can be formatted as "1/4/2017"