How to Convert Text Dates with Time to Real Excel Date Values
Question details
The user needs to convert text strings formatted as dates and times into functional serial date-time values that Excel can recognize and compute.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing system data where dates and times (e.g., mm/dd/yyyy hh:mm:ss AM/PM) are pasted or downloaded as text, preventing them from being sorted or calculated properly.
- Observed behavior
- The spreadsheet application treats the imported date and time data as plain text strings rather than numeric date-time serial numbers, causing errors in time-based calculations and chronological sorting.
Ensure your text dates follow a consistent pattern and verify there are no hidden leading or trailing spaces that could interfere with formula extraction.
Use DATE and TIMEVALUE Formulas for Fixed-Length Dates
Decompose the text string into individual year, month, day, and time components, then rebuild them into a recognized serial value using Excel functions.
This formula approach is ideal when your text dates consistently use two digits for both the month and the day (e.g., 01/09/2021 08:23:50 PM). It uses text-slicing functions (MID, LEFT, RIGHT) to isolate the elements.
Click on an empty cell where you want the converted date and time to appear.
Assuming your text date is in cell B2, type the following formula exactly: =DATE(MID(B2,7,4),LEFT(B2,2),MID(B2,4,2))+TIMEVALUE(RIGHT(B2,11))
Press Enter. The cell will display a numerical value representing the Excel date and time serial number.
Right-click the calculated cell, choose 'Format Cells', select 'Custom' or 'Date', and apply a format like 'mm/dd/yyyy hh:mm AM/PM' to display the result correctly.
Automate Variable-Length Conversions Using Power Query
For text strings with single-digit months or days (e.g., 1/9/2021 8:23 PM), use Power Query to automatically parse and convert the data type.
Convert Text Dates Efficiently in WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time formulas, as well as powerful data parsing tools. You can effortlessly clean, transform, and format imported text data into valid, calculable values.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your text dates.
- 2. Apply the date formula: In an adjacent column, type your parsing formula (e.g., =DATE(MID(B2,7,4),LEFT(B2,2),MID(B2,4,2))+TIMEVALUE(RIGHT(B2,11))) and press Enter.
- 3. Format the result: Right-click the new cell, click 'Format Cells', and select your desired Date and Time format.
- 4. Batch convert: Double-click the fill handle in the bottom-right corner of the cell to apply the conversion down the entire column.
Frequently Asked Questions
Why does Excel show my date as a 5-digit number instead of a date?
Excel stores real dates as sequential serial numbers to allow mathematical calculations (e.g., subtracting one date from another). To fix the display, simply right-click the cell, select 'Format Cells', and apply a 'Date' format.
How do I fix a #VALUE! error when using the DATE formula on text?
A #VALUE! error usually indicates that the text string does not match the exact character length expected by the MID, LEFT, or RIGHT functions. Check your data for single-digit days/months or hidden trailing spaces. If your lengths vary, consider using Power Query or the Text to Columns feature instead.
Can I use 'Text to Columns' to fix text dates?
Yes. Select the column of text dates, navigate to Data > Text to Columns, choose 'Delimited', and click Next until Step 3. Under 'Column data format', choose 'Date' (e.g., MDY) and click Finish. This will force the spreadsheet to re-evaluate the text as proper dates.
