How to convert a text cell to dates in Excel?

Here’s a question from a colleague:

I’ve received a spreadsheet containing employee information, including their start dates. However, these dates are in text format and use different styles (e.g., “01/15/2023”, “15-Jan-2023”, “2023-01-15”). I need to convert all these text entries to proper Excel date format for analysis and reporting. How can I efficiently standardize these dates in Excel while ensuring accuracy across different formats?

Example Data

For this simple example we will use the following data in comma-separated format:

Employee ID,Name,Start Date (Text)
001,John Doe,01/15/2023
002,Jane Smith,15-Feb-2023
003,Mike Johnson,2023-03-20
004,Sarah Williams,04/05/2023
005,Robert Brown,10-Jun-2023

Transform text to datetime cells in Excel 365

  1. Prepare your data:
  • Copy the example data above.
  • Click on the “Paste” button, then select “Use Text Import Wizard” from the dropdown menu.
  • Ensure the data is in columns A, B, and C, with headers in row 1.
  1. Identify the text formats:
  • In our example, we have three date formats:
    • MM/DD/YYYY (e.g., 01/15/2023)
    • DD-MMM-YYYY (e.g., 15-Feb-2023)
    • YYYY-MM-DD (e.g., 2023-03-20)
  1. Use a combination formula to handle mixed formats:
  • In cell D2, enter the following formula:
=IF(ISNUMBER(C2),C2,IF(ISNUMBER(DATEVALUE(C2)),DATEVALUE(C2),IF(ISNUMBER(VALUE(C2)),VALUE(C2),"")))
  • Copy the formula above to cell D6 to cover all our example data.
  • This formula attempts multiple conversion methods:
    • If the cell is already a number (Excel date), it returns that value.
    • If DATEVALUE can convert the text, it does so.
    • If the text is a valid number, it converts it.
    • If none of these work, it returns a blank cell.
  1. Format the results as date or date time format?:
  • Select cells D2:D6.
  • Right-click, choose “Format Cells”, then “Date”.
  • Select your preferred date display format (e.g., 3/14/2012 for consistency).
  • Verify your results.

Explanations

  • Excel stores dates as serial numbers, with January 1, 1900, as day 1.
  • The DATEVALUE function converts recognized date strings to Excel’s date serial numbers.
  • Our combination formula provides a robust solution for mixed date formats and pre-existing Excel dates.
  • Formatting the cells as dates ensures consistent display, regardless of the underlying serial number.

Can’t convert text to dates in Excel

  • If dates appear as ##### in cells, widen the column.
  • For dates before 1900, use 1904 date system: File > Options > Advanced > Use 1904 date system.
  • Regional settings can affect date interpretation. Ensure your Excel’s region matches your data’s format.
  • If certain dates fail to convert, check for hidden characters or spaces using the TRIM and CLEAN functions.
  • For very specific custom date formats not covered here, you may need to use text functions (LEFT, MID, RIGHT) to parse the date components before conversion.