Description
Use this function to return the numeric date value for a date written in either year/month/day or month/day/year format. Supported in Chains.
Syntax
DATEVALUE(YYYY/MM/DD)
orDATEVALUE(MM/DD/YYYY)
Inputs
This function has the following arguments:
| Name | Required | Valid input |
|---|---|---|
YYYY |
Yes | A four-digit year. (A two-digit year is presumed to be prefixed with "19".) This must be an integer or a formula which results in an integer in the range 1900–9999. |
MM |
Yes | A two-digit month. (A single digit is acceptable.) This must be an integer or a formula which results in an integer in the range 1–12. |
DD |
Yes | A two-digit day (A single digit is acceptable.) This must be an integer or a formula which results in an integer in the range 1–31. |
Examples
Sample data
| A | B | |
|---|---|---|
| 1 | 2021/06/03 |
10/15/2024 - Invoice #1001 |
| 2 | 2024/06/03 |
03/15/2024 - Invoice #1002 |
| 3 | 12/19/2021 |
09/14/2025 - Invoice #1021 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Determine the numeric (serial) value for a specific date. | =DATEVALUE("2021/06/03") |
This formula returns the value of the supplied date: 44261 (3 June 2021) |
| Determine the numeric (serial) value for the date 45 days before the specified date. | =DATEVALUE("1960/08/06")-45 |
This formula returns the value of the date 45 days prior to the date supplied: 22134 (6/22/1960) |
| Determine the numeric (serial) value the date 28 days after a date referenced in a cell. | =DATEVALUE(A3)+28 |
This formula returns the #VALUE! error, because cell references are not supported. |
| Extract the date form a cell that has additional content. | =DATEVALUE(LEFT(B1,10)) |
This formula uses LEFT to return the first 10 characters in B1 (the date characters), and then applies DATEVALUE to convert the result. For this data set, this would produce: 45580 (10/15/2024) |
Notes
- The entire date must be in quotation marks.
- Cell references aren't supported.
Tips
- Applying a "Date" format to the cell will display the date in MM/DD/YYYY format.
Related Functions
DATE
DATEVALUE
DAY
DAYS
DAYS360
EDATE
EOMONTH
MONTH
NETWORKDAYS
NETWORKDAYS.INTL
WEEKDAY
WORKDAY
WORKDAY.INTL
YEAR
YEARFRAC