The DATEVALUE Function in Apple Numbers Explained

Numbers has the DATEVALUE function for compatibility purposes. That's the only reason to use it.

Compatibility between spreadsheets programs is a genuine concern. While it’s not on the forefront of everyone’s mind, it is something you should be mindful of if working with others who use Excel. Thankfully, Apple Numbers maintains this compatibility and the DATEVALUE function is one example. In this post, I’ll explain the one and only reason you’ll need to use this function.

Things to Know

• The DATEVALUE function in Apple Numbers returns a date/time value for a given date string. You can format this date/time anyway you want.
• For compatibility with Excel, Numbers includes the DATEVALUE function. There is no utility for this function beyond that.
• For context, this same function in Excel adjust dates that are stored as text into a format that Excel recognizes as a serial number representing dates. Numbers organizes dates differently, so you can enter dates as normal even if you share with someone using Excel.

What is the DATEVALUE Function in Apple Numbers?

The DATEVALUE function returns a date/time value for a given date string. It will ask for the following argument as shown in the screenshot below:

• Date-Text

This function is provided for compatibility with Excel because that spreadsheet program organize dates differently than Numbers.

Of course, you are free to use this function if you want to, but there is no additional utility of doing so. And while some of you may be thinking, “But wait, that’s not 100% true. You can use this to convert dates that are stored as text into a date format.” Yes, that’s technically true. You can use this function to do that, however it is ultimately unnecessary. I will show you why in my examples below.

Not Mandatory to Use Specifically for Compatibility with Excel

If you're entering a date in Numbers and intend to share your spreadsheet with someone who uses Excel, continue to enter the date normally. There is no need to use this function. You can continue to enter dates as normal without the worry of compatibility as the compatibility is specifically when this function is used in Excel.

How to Use the DATEVALUE Function in Apple Numbers: Conceptual Example

Now, we'll go over our conceptual example. We are going to use the DATEVALUE function and fulfill its argument.

Step 1: Enter the Date-Text

The first and only argument of this function is to select the date-text. In our example, the date-text is in cell A2, so this should be the cell you select.

Step 2: The Results

The results of our inputs in cell A1 gives us “1/27/03” in cell B2 as this is the equivalent of the date text shown, but in a date format. Note the result in cell B2 has a "M/DD/YY" format. You can adjust this display format under the Data Format options to another desired format.

For the other entires in cells A3, A4, and A5, we get the following results when copying down this formula in column B :

• A3 = The result in B3 returns the same format as B2 even though the word “Feb” for February has an abbreviation.
• A4 = The result in B4 comes back in the same format as B2 and B3 even though the text formatting is in a MM/D/YYYY format.
• A5 = The result in B5 comes back as an error because the DATEVALUE formula requires a string to be a date/time value and found something not in that type of format.

How to Use the DATEVALUE Function in Apple Numbers: The Real World Example

Now that you understand how the DATEVALUE function in Apple Numbers works conceptually, let’s discuss a real world example.

The Premise

You received a pre-existing spreadsheet that someone made in Excel and you are going to view it in Apple Numbers.

The Issue

In this case, there is no issue as Apple Numbers has included the DATEVALUE function for computability with other spreadsheets.

The Solution

Continue using the spreadsheet in Apple Numbers without concern for compatibility for this function.

Tips and Reminders for the DATEVALUE Function

Some tips and reminders to remember when using the DATEVALUE function:

• Returns a date/time value for a given date string
• Is provided for compatibility with Excel.
• The argument used for this function must be in a recognizable date like format. Otherwise, the function will return an error.

Conclusion and Final Thoughts

The DATEVALUE function really has only one purpose: To provide compatibility with Excel. Outside of that, I can’t picture a situation that would warrant its use. If someone sends you a spreadsheet with this function in Excel and you open it in Numbers, you'll know it's for certain the spreadsheet will continue to be compatible (and vice versa).

For more information on this specific function, visit the Apple Support guide for DATEVALUE.