Excel Convert Date String to Datetime

To convert a date string to a real datetime value in Excel, use the DATEVALUE function for dates only, or combine DATEVALUE with TIMEVALUE for strings that include a time. This article explains the methods, common pitfalls, and step-by-step examples so you can clean up imported text dates quickly.

Why Excel Stores Dates as Numbers

Excel treats dates as serial numbers, where 1 equals January 1, 1900, and each whole number adds one day. Times are fractional parts of that number (e.g., 0.5 is noon). When you import data from another system, dates often arrive as text strings like “2025-03-14” or “14/03/2025 13:45”. Excel cannot perform date arithmetic, sorting, or formatting on text, so you must convert it to a real datetime value.

Using DATEVALUE and TIMEVALUE

DATEVALUE converts a date string to the serial number for that date. TIMEVALUE converts a time string to a decimal fraction of a day. To handle a combined date and time, add the two results together:

• =DATEVALUE("2025-03-14") returns 45730 (the serial for March 14, 2025).
• =TIMEVALUE("13:45") returns 0.5729167.
• =DATEVALUE("2025-03-14") + TIMEVALUE("13:45") returns 45730.5729167.

After entering the formula, format the cell as a date and time (e.g., yyyy-mm-dd hh:mm) to see the readable result.

Step-by-Step Conversion with a Formula

Assume cell A1 contains the text “2025-03-14 13:45”. To convert it:

1. In B1, enter =DATEVALUE(LEFT(A1,10)) + TIMEVALUE(RIGHT(A1,5)).
2. Press Enter. Excel returns a serial number.
3. Right-click B1, choose Format Cells, then pick a date/time format.

If your string uses a different separator or order (e.g., “14/03/2025 13:45”), adjust the LEFT and RIGHT lengths accordingly. For strings with seconds, use MID to extract the time part.

Using Text to Columns for Bulk Conversion

For a column of text dates, use the built-in Text to Columns wizard:

1. Select the column with the text dates.
2. Go to Data → Text to Columns.
3. Choose Delimited, then click Next.
4. Uncheck all delimiters (or keep the one that separates date and time, like a space).
5. In Step 3, choose Date and select the order (e.g., YMD, DMY).
6. Click Finish. Excel converts the text to real dates.

This method is fast and handles large datasets, but it modifies the original data in place. Make a backup copy first.

Common Problems and How to Fix Them

• DATEVALUE returns #VALUE! – The string is not recognized as a valid date. Check the system’s date settings; Excel expects dates based on your locale (e.g., US uses MM/DD/YYYY, many other regions use DD/MM/YYYY).
• Leading zeros lost – If the source string has leading zeros (e.g., “03/05/2025”), they may be dropped when imported. Use a formula to re-add them: =DATEVALUE(TEXT(A1,"mm/dd/yyyy")).
• Dates appear as numbers after conversion – This is normal; just apply a date format.
• Time zone or UTC offsets – Excel does not handle time zones automatically. You may need to subtract or add hours manually.

Alternatives: Power Query and Flash Fill

Power Query (Get & Transform) is ideal for recurring conversions. Load the data, select the column, and change the data type to Date or Date/Time. Power Query parses most common formats automatically and lets you specify a locale.

Flash Fill (Ctrl+E) can also work for simple patterns. Type the desired output in the first cell, then press Ctrl+E. It guesses the pattern and fills the rest, but it can be unreliable with irregular data.

Frequently Asked Questions

Why does my date string convert to a wrong date?
This usually happens because your system’s date order (e.g., DD/MM/YYYY) differs from the string’s order (e.g., MM/DD/YYYY). Use the DATE function to specify the order explicitly: =DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2)).

Can I convert a date string without a formula?
Yes. Use Text to Columns, Flash Fill, or Power Query. These tools detect patterns and convert text to dates without writing formulas.

How do I convert a date string that includes a time zone like “2025-03-14T13:45:00Z”?
Excel cannot parse the “T” and “Z” directly. Use a formula to strip them: =DATEVALUE(LEFT(A1,10)) + TIMEVALUE(MID(A1,12,8)). Then adjust for the time zone offset manually if needed.

What if my date string is in a different language?
Excel’s DATEVALUE depends on your system’s language. If the month names are in another language, you may need to replace them with numbers using SUBSTITUTE, or use Power Query with a locale setting.

Is there a way to convert a column of text dates to real dates permanently?
Yes. After converting with any method, copy the cells and paste as values (Paste Special → Values) to replace the text with the actual serial numbers. Then delete the original text column.

Related

• Epic Games Loading Your Order? Fix It Now
• Windows 11 Says No Updates Available But Not Up to Date
• Limit Retail Price Tracking: A How-To Guide