Description
Use this function to get a numeric date value for a given year, month, and day. Supported in Chains.
Syntax
DATE(YYYY/MM/DD)
orDATE(MM/DD/YYYY)
Inputs
This function has the following fields. They can be in the order "YYYY/MM/DD" or "MM/DD/YYYY":
| Name | Required | Valid input |
year |
Yes | A four-digit year. (A two-digit year is presumed to be prefixed with "19".) This can be an integer or a formula which results in an integer in the range 1900–9999, or a cell reference. Quotation marks are not required. |
month |
Yes | A two-digit month. (A single digit is acceptable.) This can be an integer or a formula which results in an integer in the range 1–12, or a cell reference. Quotation marks are not required. |
day |
Yes | A two-digit day (A single digit is acceptable.) This can be an integer or a formula which results in an integer in the range 1–31, or a cell reference. |
Examples
Sample data
| A (date) |
B (year) |
C (month) |
D (day) |
|
|---|---|---|---|---|
| 1 | 28/2/2023 | 2024 | 1 | 23 |
| 2 | 36711 | 2000 | 12 | 11 |
| 3 | 2003/7/19 | 2003 | 7 | 19 |
| 4 | 24/12/2013 | 2024 | 12 | 24 |
Sample formulas
| Use case | Formula | Explanation and Result | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Insert a date as a fixed value. | =DATE(2023,4,6) |
This formula returns the value of the supplied date of April 6, 2023, which is 45022. | ||||||||||
| Return the date value of a specified cell. | =DATE(A3) |
This formula returns the value of the date identified by the supplied values in cell A3. For this data set, it would return 37821 (7/19/2003). | ||||||||||
| Return the value of the date identified by the values in the specified cells. | =DATE(B2,C2,D2) |
This formula returns the value of the date identified by the supplied values in cells B2,C2,and D2. For this data set, it would return 36871 (12/11/2000). | ||||||||||
| Create rolling month-end dates. | =EOMONTH(DATE(B2,C2,1),0) |
This formula combines DATE with EOMONTH to return rolling month-end dates. For this data set, if the formula was in column E, it returns the following values. For the purposes of this illustration, the column next to it shows the dates in date format.
|
||||||||||
| Calculate fiscal year dates | =EDATE(DATE(2024,7,1),3) |
This formula combines DATE with EDATE to return a fiscal year date. This returns the value of 45566 (October 1, 2024), which is 3 months after July 1. | ||||||||||
| Calculate a date in the future from a date in cells. | =DATE(B1,(C1+6),D1) |
This formula adds 6 months to the date in B1.
For this data set, it returns 45496 (7/23/2024). |
||||||||||
| Calculate a date in the past from a cell containing a standard date. | =DATE(YEAR(A1),MONTH(A1),DAY(A1)-14) |
This formula uses the YEAR, MONTH, and DAY functions to break apart the date in cell A1, and then subtracts 14 days from that day. DATE then combines the values to produce the desired date. For this data set, it returns 45616 (11/20/2024). |
||||||||||
| Return the day of the week name for a specified date. | =TEXT(DATE(2025,3,3),"dddd") |
This formula uses DATE to create a date value for March 3, 2025, and then uses the TEXT function to format that date as the full name of the day of the week. For this data set, it returns Monday. |
Related functions
DATE
DATEVALUE
DAY
DAYS
DAYS360
EDATE
EOMONTH
MONTH
NETWORKDAYS
NETWORKDAYS.INTL
WEEKDAY
WORKDAY
WORKDAY.INTL
YEAR
YEARFRAC