How to Convert Text to Dates in Excel: A Practical Guide

Have you ever imported data into Excel only to find that date columns are showing up as text? This common issue can cause headaches when you need to sort, filter, or calculate date differences. In this article, we’ll explore why dates appear as text in Excel, provide step-by-step solutions for conversion, and even look at a VBA alternative.

Why Dates Show Up as Text in Excel

There are several reasons why dates might appear as text instead of actual date values:

• The source data has dates formatted as text
• The data was imported or copied incorrectly
• Cells were formatted as text before the data was entered

Step-by-Step Solution to Convert Text to Dates

Method 1: Using Excel’s Date Conversion Functions

Excel has built-in functions that can help convert text strings to date values.

Example 1: Simple Text to Date Conversion

If your dates are in a standard format like “2023-09-15”, you can use the DATEVALUE function:

=DATEVALUE("2023-09-15")

This will return 44786, which is Excel’s serial number for that date. To display it as a date, format the cell as “Date”.

Example 2: Converting Custom Formatted Dates

For dates in non-standard formats, use the LEFT, MID, and RIGHT functions to extract day, month, and year parts:

=DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2))

This formula assumes your date is formatted as “DDMMYYYY”. Adjust according to your format.

Method 2: Using Power Query

For larger datasets, Power Query provides a robust way to convert text to dates:

1. Select your data range and go to Data > From Table/Range
2. In the Power Query Editor, select your date column
3. Go to Transform > Data Type > Using Locale, then choose Date/Time
4. Click Close & Load to apply changes

Advanced: Using VBA for Text to Date Conversion

If you frequently need to convert text to dates, a VBA macro can automate the process:

Sub ConvertTextToDate()
    Dim ws As Worksheet
    Dim cell As Range

    Set ws = ActiveSheet

    For Each cell In ws.UsedRange
        If IsNumeric(cell.Value) And Len(cell.Value) = 8 Then
            cell.Value = DateSerial(Right(cell.Value, 4), Mid(cell.Value, 5, 2), Left(cell.Value, 2))
            cell.NumberFormat = "mm/dd/yyyy"
        End If
    Next cell

    MsgBox "Conversion complete!"
End Sub

This macro assumes dates are in the format DDMMYYYY and will convert them to date values with MM/DD/YYYY formatting.

Common Mistakes & Tips

• Avoid using general formulas like VALUE() which might not correctly interpret date formats
• Always double-check date formats in imported data
• Use Power Query for large datasets to maintain consistency

Tool Recommendation: CelTools for Excel

CelTools offers over 70 extra features for auditing, formulas, and automation. It can help with data cleaning tasks like converting text to dates more efficiently.

Conclusion

Converting text to date in Excel doesn’t have to be a headache. With the right functions, Power Query, or even VBA macros, you can streamline this process and ensure your data is always in the correct format.

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical