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 | Description | Valid input |
|---|---|---|---|
year |
Yes | A four-digit year. (A two-digit year is presumed to be prefixed with "19".) | 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.) | 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.) | 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) |
Result: 45022. This is the value of the supplied date of April 6, 2023. | |||||||||||||||
| Return the value of the date identified by the values in the specified cells. | =DATE(B2,C2,D2) |
Result: 36871 (12/11/2000). This is the value of the date identified by the supplied values in cells B2,C2,and D2. |
|||||||||||||||
| Create rolling month-end dates. | =EOMONTH(DATE(B2,C2,1),0) |
This formula combines DATE with EOMONTH to return rolling month-end dates. Result: 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. Result: 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. Result: 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. Result: 44971 (2/14/2023). |
|||||||||||||||
| 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. Result: Monday. |