The DATEVALUE function in Excel converts a date that is formatted as text into a serial number that represents the date in Excel’s date system. This function is useful when you need to recalculate dates that are stored as text, such as imported data from other sources.
Syntax and arguments
The syntax of the DATEVALUE function is:
=DATEVALUE(date_text)
The argument of the function is:
date_text: A text string that represents a date in a valid Excel date format. For example, “1/1/2024” or “January 1, 2024”.
How to use the DATEVALUE function
To use the DATEVALUE function, follow these steps:
- Enter the date that is formatted as text in a cell, such as A1.
- In another cell, such as B1, enter the formula
=DATEVALUE(A1). - Press Enter to get the result. The result is a serial number that corresponds to the date in Excel’s date system. For example, if A1 contains “1/1/2024”, the result in B1 is 44576.
- To display the result as a date, you can format the cell as a date. To do this, right-click on the cell, select Format Cells, choose Date from the Category list, and select the desired date format from the Type list. Click OK to apply the format. The result in B1 will now show as “1/1/2024” or another date format that you chose.
Example
Let’s say you have a table of data that contains dates that are formatted as text, such as “Jan-24” or “24-Jan”. You want to recalculate these dates and sort them in chronological order. Here is how you can use the DATEVALUE function to do this:
- Assume the table of data is in the range A1:B10, where column A contains the dates as text and column B contains some values associated with the dates.
- In cell C1, enter the formula
=DATEVALUE(A1). This will convert the date in A1 into a serial number. - Copy the formula down to the rest of the cells in column C. This will convert all the dates in column A into serial numbers.
- Select the entire table, including the headers, and click on the Data tab. Click on the Sort button and choose to sort by column C in ascending order. Click OK to sort the table by the recalculated dates.
- Optionally, you can format column C as a date to display the serial numbers as dates. You can also hide column C if you don’t want to show it in the table.
Here is an example of the table before and after using the DATEVALUE function and sorting by the recalculated dates:
| Date | Value | Recalculated Date |
|---|---|---|
| Jan-24 | 100 | 44197 |
| Feb-24 | 200 | 44228 |
| Mar-24 | 300 | 44256 |
| Apr-24 | 400 | 44287 |
| May-24 | 500 | 44317 |
| Jun-24 | 600 | 44348 |
| Jul-24 | 700 | 44378 |
| Aug-24 | 800 | 44409 |
| Sep-24 | 900 | 44440 |
| Oct-24 | 1000 | 44470 |
| Date | Value | Recalculated Date |
|---|---|---|
| Jan-24 | 100 | 1/1/2024 |
| Feb-24 | 200 | 2/1/2024 |
| Mar-24 | 300 | 3/1/2024 |
| Apr-24 | 400 | 4/1/2024 |
| May-24 | 500 | 5/1/2024 |
| Jun-24 | 600 | 6/1/2024 |
| Jul-24 | 700 | 7/1/2024 |
| Aug-24 | 800 | 8/1/2024 |
| Sep-24 | 900 | 9/1/2024 |
| Oct-24 | 1000 | 10/1/2024 |
Other approaches
There are other ways to convert dates that are formatted as text into serial numbers in Excel, such as:
- Using the DATE function with the YEAR, MONTH, and DAY functions to extract the year, month, and day from the text date and create a valid date. For example,
=DATE(YEAR(A1),MONTH(A1),DAY(A1)). - Using the VALUE function to convert the text date into a numeric value. For example,
=VALUE(A1). - Using the Text to Columns feature to split the text date into separate columns and then combine them into a valid date. For example, if the text date is in the format “dd-mmm”, you can use the Text to Columns feature to split it into two columns, one for the day and one for the month. Then, you can use the DATE function to create a valid date by adding the current year. For example,
=DATE(YEAR(TODAY()),B1,C1).
