Excel DATE Function
The Excel DATE function returns a date through the use of individual year, month and day parameters
Example: Excel DATE Function
|
=DATE(2017,3,15)
|
Result in cell E5 (15/03/2017) - returns a date for a 15th day in the 3rd month of 2017.
|
|
=DATE(2017,17,15)
|
Result in cell E6 (15/05/2018) - returns a date for a 15th day in the 14th month of 2017. Given that we have specified 14 months for 2017 the formula will recognise five extra months that will be rolled into next year. Therefore, it will automatically change the year from 2017 to 2018 as the additional 5 months roll up part of 2018.
|
|
=DATE(2017,3,-7)
|
Result in cell E7 (21/02/2017) - returns a date that is seven days before the first of March (third month) of 2017. Therefore, the formula will automatically change the nominated month from March to February.
|
METHOD 2. Excel DATE Function using links
EXCEL
|
=DATE(B5,C5,D5)
|
Result in cell E5 (15/03/2017) - returns a date for a 15th day (cell (D5)) in the 3rd month (cell (C5)) of 2017 (cell (B5)).
|
|
=DATE(B6,C6,D6)
|
Result in cell E6 (15/05/2018) - returns a date for a 15th day (cell (D6)) in the 14th month (cell (C5)) of 2017 (cell (B5)). Given that we have specified 14 months for 2017 the formula will recognise five extra months that will be rolled into next year. Therefore, it will automatically change the year from 2017 to 2018 as the additional 5 months roll up part of 2018.
|
|
=DATE(B6,C6,D6)
|
Result in cell E7 (21/02/2017) - returns a date that is seven days (cell (D7)) before the first of March (cell (C7)) (third month) of 2017 (cell (B7)). Therefore, the formula will automatically change the nominated month from March to February.
|
METHOD 3. Excel DATE function using the Excel built-in function library with hardcoded values
EXCEL
Formulas tab > Function Library group > Date & Time > DATE > populate the input boxes
| =DATE(2017,3,15) Note: in this example we are populating all the input boxes associated with the DATE function including year, month and day to return a date. |
 |
METHOD 4. Excel DATE function using the Excel built-in function library with links
EXCEL
Formulas tab > Function Library group > Date & Time > DATE > populate the input boxes
| =DATE(B5,C5,D5) Note: in this example we are populating all the input boxes associated with the DATE function by referencing to the cells that contain the numeric values representing the year (cell (B5)), month (cell (C5)) and day (cell (D5)) that we want to convert into a date format. |
 |
Sub Excel_DATE_Function_Using_Hardcoded_Values()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("DATE")
'apply the Excel DATE function
ws.Range("E5") = DateSerial(2017, 3, 15)
ws.Range("E6") = DateSerial(2017, 17, 15)
ws.Range("E7") = DateSerial(2017, 3, -7)
ws.Range("E5") = DateSerial(2017, 3, 15)
ws.Range("E6") = DateSerial(2017, 17, 15)
ws.Range("E7") = DateSerial(2017, 3, -7)
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named DATE.
Worksheet Name: Have a worksheet named DATE.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E5"), ("E6") and ("E7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 2. Excel DATE function using VBA with links
VBA
Sub Excel_DATE_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("DATE")
'apply the Excel DATE function
ws.Range("E5") = DateSerial(ws.Range("B5"), ws.Range("C5"), ws.Range("D5"))
ws.Range("E6") = DateSerial(ws.Range("B6"), ws.Range("C6"), ws.Range("D6"))
ws.Range("E7") = DateSerial(ws.Range("B7"), ws.Range("C7"), ws.Range("D7"))
ws.Range("E5") = DateSerial(ws.Range("B5"), ws.Range("C5"), ws.Range("D5"))
ws.Range("E6") = DateSerial(ws.Range("B6"), ws.Range("C6"), ws.Range("D6"))
ws.Range("E7") = DateSerial(ws.Range("B7"), ws.Range("C7"), ws.Range("D7"))
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named DATE.
Year, Month and Day: Have the year, month and day populated in columns B, C and D, respectively.
Worksheet Name: Have a worksheet named DATE.
Year, Month and Day: Have the year, month and day populated in columns B, C and D, respectively.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E5"), ("E6") and ("E7") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Year, Month and Day: Select the year, month and day that you want to convert to a date by changing the relevant cell references to any cell in the worksheet that contains the values that you want to apply against the year, month or day and doesn't conflict with the formula.
DESCRIPTION
The Excel DATE function returns a date through the use of individual year, month and day parameters.
The Excel DATE function returns a date through the use of individual year, month and day parameters.
SYNTAX
=DATE(year, month, day)
=DATE(year, month, day)
ARGUMENTS
year: (Required) The number that represents a year. This needs to be between two and four digits.
month: (Required) The number that represents a month.
day: (Required) The number that represents a day.
year: (Required) The number that represents a year. This needs to be between two and four digits.
month: (Required) The number that represents a month.
day: (Required) The number that represents a day.
ADDITIONAL NOTES
Note 1: If the year value is between 0 and 1899 the DATE function will add the nominated year value to 1900.