Numbers DATEVALUE: Convert Date Text and Check Calendar Components
Convert recognized date strings in Numbers with explicit locale limits, year-month-day controls and a structured DATE comparison without serial-number assumptions.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
DATEVALUE converts a recognized date string, or an existing date/time value, to a date/time value. Apple describes it as a compatibility function for other spreadsheet apps. It remains documented; that description does not mean it is retired.
A date-looking string and a date value can look similar in a cell. The practical checks below extract the year, month and day so the result is more explicit than its display format.
Availability and version history
- Introduced: The original introduction release and date are not established by the checked references.
- Availability: Currently documented in Apple’s Functions Help 15.4 documentation edition; no final supported-version endpoint is established.
- Scope: Shared iWork reference. Individual historical Mac, iPad, iPhone and iCloud endpoints and imported-workbook parity remain unverified.
- Checked: October 14, 2026, against the official DATEVALUE reference. Calendar, time-fraction and text checks were independently calculated; no native Numbers test was performed.
Syntax and locale-sensitive text
DATEVALUE(date-text)
The DATEVALUE reference accepts a date string or a date/time value and returns an error for an invalid date. It does not specify the exact error token in that rule. There is one argument and no documented default.
The text must be recognized as a date in the intended settings. A string such as 4/6/2026 is ambiguous across date conventions. Apple’s DATE reference recommends numeric year, month and day arguments to avoid that text-format ambiguity.
The slash-form text exercises below use a month/day/year interpretation. Confirm that the document and system date settings recognize that convention before comparing the expected components. Four-digit years avoid an unstated two-digit-year rule. Changing the visible number format cannot by itself establish how an ambiguous input was parsed.
Preserve the text and check the converted calendar components
Create a table named ImportedDates, with eight columns A:H and eight rows, including one header row. Put Source text, Converted date, Year, Month, Day, Is date, Is source text and Calendar key in A1:H1.
Set A2:A5 to the Text data format before entering these strings, without enclosing quotation marks. The first two are Apple’s directly specified date-text examples; the other two are additional calendar checks under the stated month/day/year convention.
| Row | A: Source text | Expected calendar date | C | D | E |
|---|---|---|---|---|---|
| 2 | 10/16/2008 | October 16, 2008 | 2008 | 10 | 16 |
| 3 | 12/29/1979 | December 29, 1979 | 1979 | 12 | 29 |
| 4 | 2/29/2024 | February 29, 2024 | 2024 | 2 | 29 |
| 5 | 1/31/2026 | January 31, 2026 | 2026 | 1 | 31 |
Enter these formulas in row 2, then fill B2:H2 down through row 5:
- B2:
=DATEVALUE(A2) - C2:
=YEAR(B2) - D2:
=MONTH(B2) - E2:
=DAY(B2) - F2:
=ISDATE(B2) - G2:
=ISTEXT(A2) - H2:
=C2*10000+D2*100+E2
The documentation-based type expectations are TRUE in F and G for each correctly entered row. B’s visible date style follows its cell format; C:E show the intended calendar components independently of that display.
The expected H keys are 20081016,19791229,20240229,20260131. They are ordinary numbers built for comparison, not date serial numbers returned by DATEVALUE. Do not feed such a key to another app as though it were that app’s date storage value.
Reproduce the complete literal example
On a separate table LiteralDate, with four columns and four rows including a header row, enter =DATEVALUE("12/29/1979") in A2. Put =YEAR(A2) in B2, =MONTH(A2) in C2 and =DAY(A2) in D2. The expected components are 1979,12,29, matching Apple’s December 29 example.
Apple also describes converting an already entered date/time value of August 2, 1979 at 06:30 am to a displayed August 2 date. The entry emphasizes the date value and formatting. This guide does not infer an undocumented retained-time or midnight-normalization rule from the date-only display.
Advanced: build dates from structured fields instead
Create DateParts, with seven columns A:G and six rows including a header row. Put Year, Month, Day, Constructed date, Compatibility conversion, Month check and Day check in row 1. Enter:
| Row | A | B | C |
|---|---|---|---|
| 2 | 2024 | 2 | 29 |
| 3 | 2024 | 3 | 1 |
| 4 | 2014 | 11 | 10 |
In D2 enter =DATE(A2,B2,C2); E2 =DATEVALUE(D2); F2 =MONTH(E2); G2 =DAY(E2). Fill D2:G2 down through row 4. Expected month/day pairs are 2/29,3/1,11/10.
DATEVALUE is redundant here because DATE already returns a date/time value, but the example demonstrates the documented accepted type without parsing a slash-form string.
On a copy named SwappedFields, change only B4 to 10 and C4 to 11. Row 4 becomes October 11, 2014. This reproduces the distinction in Apple’s DATE examples: changing argument positions changes the calendar date, while changing display formatting does not.
Limits and troubleshooting
Invalid-date strings cause a documented error. The entry does not establish whether every impossible calendar string is rejected or normalized, nor its treatment of incomplete dates, whitespace, two-digit years or timezone-bearing strings. Do not assume that a parser in another spreadsheet makes the same choices.
If the source column became dates during import, G’s ISTEXT check will no longer describe the intended text fixture. Preserve the original strings when diagnosing a conversion. ISDATE distinguishes the returned date type, while YEAR, MONTH and DAY check its components.
The documentation edition is not a Numbers app version or a native compatibility test. English formulas use commas; Apple’s functions overview uses semicolons between arguments when a comma is the decimal separator.