The DATEVALUE Function in Apple Numbers Explained

DATEVALUE converts a date stored as text into an actual date value Numbers can work with. In practice, this function exists almost entirely for one reason: compatibility with Excel. If you open a spreadsheet someone built in Excel and see DATEVALUE in the formula bar, that's why it's there. Knowing what it does helps before you start working around it.

There's a Numbers-native angle too. If you've ever imported data from a CSV or copied rows from a web page and found that dates wouldn't sort or calculate correctly, you've run into text-formatted dates. DATEVALUE can sometimes help with that, though it isn't always the right tool depending on how the text is formatted.

Either way, most Numbers users won't write this formula from scratch very often. This post covers what it does, where Numbers and Excel behave a little differently, and the cases where you'd actually reach for it.

DATEVALUE Function Details

Purpose: Converts a date stored as text into a date/time value Numbers can use for sorting, date math, and other date functions
Result: A date/time value
Syntax: =DATEVALUE(date-text)
Arguments: date-text

• date-text = a cell reference or text string containing something that looks like a date, such as "1/27/2025" or "Jan 27, 2025"

When You'd Actually Use The DATEVALUE Function

DATEVALUE takes a piece of text that looks like a date and converts it into a real date/time value. Once a cell holds a date/time value instead of plain text, Numbers can use it in date math: comparing dates, calculating durations, sorting chronologically, or feeding it into other date functions like DATEDIF or EOMONTH.

The function accepts a range of common date formats as input. Short numeric formats, abbreviated month names, and other date format variations. Where it fails is when the text isn't resolvable to an actual calendar date. More on that in the example below.

Apple includes DATEVALUE specifically because Excel needs it. In Excel, dates that land in a spreadsheet as text (from an import, a copy-paste, a form export) won't behave like dates until converted to a fate format. DATEVALUE is that conversion tool in Excel's world. Numbers is generally better at recognizing dates the moment you type them, so the function mostly matters when files cross between programs.

Say column A contains dates typed as plain text, maybe imported from a CSV or copied from another source, and you want real date values in column B.

Column A (text input):

• A2: 1/27/03
• A3: Feb 27, 2003
• A4: 2/3/2003
• A5: 13/45/2003

Formula in B2, copied down through B5:

=DATEVALUE(A2)

Results in column B:

• B2: Returns a date value equivalent to January 27, 2003. The display format (1/27/03 or another layout) is controlled separately — see the note on display format below.
• B3: Returns the same result as B2, correctly interpreting the abbreviated month name "Feb."
• B4: Returns February 3, 2003, handling the single-digit day and four-digit year without issue.
• B5: Returns an error. The text 13/45/2003 is shaped like a date but describes an invalid date. There is no month 13 or day 45. DATEVALUE doesn't partially succeed. Either the text resolves to a real calendar date, or the cell returns an error.

Where Numbers and Excel Actually Diverge

This is the part most explanations of this function skip over, and it's the one place where "full compatibility" needs a small caveat.

In Excel, DATEVALUE returns a serial number which is a plain integer counting days from a fixed reference date. That number is how Excel handles date arithmetic under the hood; it just displays as a date when the cell is formatted that way.

In Numbers, DATEVALUE returns a date/time value, not a bare serial number. For everyday use this covers the same ground. Sorting, comparing, calculating durations between dates all work fine. But if you're porting a formula from Excel that specifically expects a raw number back from DATEVALUE, like a calculation that was built to treat the result as an integer, it may not slot in the same way. This has caught people off guard when migrating files, because the function name and stated purpose are identical in both programs, but the underlying data type isn't.

If you genuinely need a numeric value rather than a date/time value, you'll need an additional conversion step rather than relying on DATEVALUE alone.

When You'll Actually Run Into This Function

Opening an Excel file in Numbers: The most common scenario. A DATEVALUE formula already exists in the file. When you open it in Numbers, the formula stays in place and Numbers recalculates it using its own engine. You'll get a date/time value in that cell rather than Excel's serial number. For almost everything you'd do with that cell afterward like sorting by it or comparing it to another date this is a non-issue. It only matters if a downstream formula in the same sheet was written to expect a raw number. That formula, not DATEVALUE itself, is where you'd check behavior after the file moves over.

Other Use Cases

• You intentionally built a text string representing a date, such as "6/" & A1 & "/2026", and now want to convert it into a date.
• You receive text from another function or data source that isn’t already typed as a date.

Outside of these reasons, DATEVALUE is essentially redundant in Numbers. In fact, you may never have seen this function ever until you decided to look it up (way to go looking it up in iWork Expert!).

The key takeaway, this function existing in Numbers largely to:

1. maintain functional parity with other spreadsheet programs
2. help with imported or text-based data
3. improve compatibility with Excel workbooks

Tips and Reminders

• The date-text argument must contain something recognizable as a date. If the text can't be resolved to a real calendar date, the function returns an error and not a partial result.
• DATEVALUE accepts a range of common date formats: short numeric, abbreviated month names, and different separator styles all generally work.
• The result is a date/time value, not a serial number. This is the main behavioral difference from Excel's version of this function.
• Display format (i.e., mm/dd/yyyy or dd/mm/yy) is set separately from the formula, under Cell → Data Format in the Format sidebar.
• If you're entering dates yourself in Numbers, you don't need to use this function. Just type the date directly and Numbers will recognize it.

For everyday date entry in Numbers, just type the date. DATEVALUE earns its place in two narrow cases: you're opening an Excel file that already uses it, or you're cleaning up date-shaped text that came in from an import. Outside of those, it's a function you'll recognize when you see it more than one you'll write yourself.

For more detail, see Apple's support guide for DATEVALUE.

Related Functions

• DATE — builds a date from separate year, month, and day values already in their own cells. More direct than typing a date as text, and unambiguous regardless of regional date settings.
• TIMEVALUE — DATEVALUE's counterpart for time strings. Same compatibility rationale, but converts text like "3:45 PM" into a time value instead of a date.
• DAY, MONTH, YEAR — once you have a real date value, these pull out individual components. Works with any date/time value, whether it came from DATEVALUE, DATE, or direct entry.
• DATEDIF — calculates the number of days, months, or years between two dates. Useful next step once you have real date/time values to work with.