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
- 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.
- 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)
- 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.
- 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.