You have dates stored as text in Excel, and formulas like SUM or DATEDIF do not work on them. Excel cannot perform calculations on text, even if it looks like a date. This article explains how to convert these text strings into proper date serial numbers. You will learn several methods to fix this common data import problem.
Key Takeaways: Convert Text to Dates for Calculations
- Data > Text to Columns wizard: Forces Excel to re-evaluate and convert text-formatted dates in a selected column.
- DATEVALUE function: Converts a text string that looks like a standard date into a serial number you can format.
- Paste Special with Multiply operation: Converts text to numbers by multiplying by 1, which also works for dates.
Understanding Date Serial Numbers and Text Strings
Excel stores dates as sequential serial numbers. January 1, 1900, is serial number 1. This system allows Excel to add days or calculate the difference between dates. A text string is just a set of characters to Excel, like “01/15/2023”. It is left-aligned in a cell by default and cannot be used in math. The conversion process changes this text into the underlying serial number, which you can then format to display as a date.
Before you start, check your data. Select a cell with a date. Look at the formula bar. If it shows an apostrophe at the start, like ‘2023-12-25, it is text. Also, text dates are often left-aligned, while true dates are right-aligned. Ensure your system’s regional date settings match the format of your text strings. For example, “03/04/2023” is ambiguous; it could be March 4 or April 3 depending on the locale.
Methods to Convert Text Dates to Serial Numbers
You can use built-in Excel tools, functions, or simple arithmetic to transform text into dates. The best method depends on your data’s consistency and your preferred workflow.
Using the Text to Columns Wizard
This tool is reliable for bulk conversion of an entire column. It does not split your data if configured correctly.
- Select the data column
Click the column letter to select all cells containing the text dates. - Open the Text to Columns wizard
Go to the Data tab on the ribbon. Click the Text to Columns button. - Choose the file type
In the wizard, keep “Delimited” selected and click Next. Uncheck all delimiters on the next screen and click Next again. - Set the column data format
In the final step, select the “Date” option under Column data format. Choose the format that matches your text strings from the dropdown, like MDY for month/day/year. Click Finish.
Using the DATEVALUE Function
The DATEVALUE function takes a text date and returns its serial number. The syntax is =DATEVALUE(date_text). For example, =DATEVALUE(“2023-12-25”) returns 45291. If your text date is in cell A1, use =DATEVALUE(A1). The result will be a number. You must then format the cell as a date via Home > Number Format > Short Date.
Using Paste Special to Multiply by 1
A mathematical operation forces Excel to convert text to a number. This trick also works for date text.
- Enter the number 1 in a blank cell
Type 1 and press Enter. Then copy that cell by pressing Ctrl+C. - Select your text dates
Highlight the range of cells containing the dates stored as text. - Open the Paste Special dialog
Right-click the selected range, choose Paste Special, then click Paste Special again from the sub-menu. - Choose the Multiply operation
In the dialog, under Operation, select Multiply. Click OK. This multiplies all selected cells by 1, converting text to serial numbers. - Apply date formatting
With the range still selected, go to Home > Number Format and choose a date format.
Common Mistakes and Data Issues to Avoid
Converting dates can fail if the text is not in a recognizable pattern. Here are specific problems and how to solve them.
DATEVALUE Returns a #VALUE! Error
This error means Excel does not recognize your text as a date. The text might have extra spaces, use dots as separators like “01.12.2023”, or be in a format not supported by your system locale. First, use the TRIM function to remove spaces: =DATEVALUE(TRIM(A1)). For non-standard separators, use the SUBSTITUTE function to replace dots with slashes: =DATEVALUE(SUBSTITUTE(A1, “.”, “/”)).
Text to Columns Does Not Change the Format
If the cells remain as text after using Text to Columns, Excel’s workbook calculation might be set to manual. Press F9 to force a calculation. Also, check if the cells were pre-formatted as Text. Select the column, go to Home > Number Format, and choose General or a date format. Then re-apply the Text to Columns wizard.
Converting Dates With Two-Digit Years
Text like “1/5/23” can be ambiguous. Excel interprets two-digit years based on a Windows setting. Years 00 through 29 are treated as 2000-2029. Years 30 through 99 are treated as 1930-1999. To avoid confusion, use four-digit years in your source data. If you receive two-digit year data, the Text to Columns wizard will apply the correct century based on your system settings during conversion.
Comparison of Conversion Methods
| Item | Text to Columns Wizard | DATEVALUE Function | Paste Special Multiply |
|---|---|---|---|
| Best for | Converting an entire column at once | Creating a new calculated column of dates | Quick in-place conversion without formulas |
| Changes original data | Yes, directly in the source cells | No, result is in a new cell | Yes, directly in the source cells |
| Handles varied text formats | Good, with manual format selection | Poor, requires uniform recognizable format | Poor, only works on numeric text |
| Speed for large datasets | Fast | Slower, requires formula calculation | Fast |
| Requires reformatting | Usually applies date format automatically | Yes, must format result as a date | Yes, must format result as a date |
You can now convert date text strings into serial numbers for calculations. Use Text to Columns for a clean, permanent fix on a full column. Try the VALUE function as an alternative to DATEVALUE for more general text-to-number conversion. For advanced date analysis, explore the EDATE function to calculate dates a specific number of months in the future or past.