How to Easily Convert Text to Dates in Excel

Dates are essential in Excel for calculating periods, identifying trends, and visualizing time-based patterns in data. However, dates imported from other sources or manually entered often get formatted as text rather than date values. Before using such data for date calculations, you need to convert text to dates in Excel.

The good news is, changing text to dates is straightforward with Excel’s powerful inbuilt tools In this guide, we’ll cover

• Why text formatting happens for dates
• How to identify text dates
• Step-by-step methods to convert text to dates
• Fixes for text that won’t convert
• Tips to automate date conversion in bulk

So let’s get started transforming those text strings into proper dates for timely data insights!

Common Reasons for Date Values Stored as Text

Before learning how to change text to dates, it’s helpful to understand the key reasons why dates lose their original date formatting in Excel:

• Importing data from websites CSVs or databases can bring in dates as text strings.

• Manually typing dates using quotation marks or apostrophes signals Excel to store them as text

• Copying dates from sources like webpages and pasting into Excel strips the date formatting.

• Using the Text format command on cells changes dates to text losing associated formatting.

• Formulas like =CONCATENATE may combine dates with text, storing the output as text.

Being aware of what causes the date-to-text conversion helps avoid similar issues when entering new data.

How to Identify Dates Stored as Text in Excel

Before conversion, it’s crucial to identify cells containing dates as text strings. Here are some quick ways to check:

• Try formatting – Text formatted cells won’t change when you apply Date formats

• Look for green triangles – Excel flags text dates with a small green triangle for errors

• Check formulas – If a cell contains =TEXT(A2,”mm/dd/yyyy”) it’s explicitly text

• See alignment – Text aligns left unlike dates which align right

• Examine values – Valid dates like 2/15/2022 stored as text lack slashes

Once you confirm date text cells, transform them into proper dates using the methods below.

Convert Text to Dates by Changing Format

The most straightforward way is to apply Date formatting:

1. Select the cells containing text dates

2. Open the Home tab and click the Number Format drop down

3. Choose Date and pick a formatting style like 1/31/2002 or 31-Jan-02

The built-in Date formats automatically convert text strings into serial date values ready for date calculations.

Use the DATEVALUE Function

Another quick way is to use Excel’s DATEVALUE function:

1. Select a blank cell and type =DATEVALUE(

2. Click the text date cell to reference

3. Add a closing ) and press Enter

DATEVALUE converts text dates into serial numbers that Excel recognizes as dates. You can drag the formula down to change entire columns quickly.

Paste Special to Convert Text to Dates

The Paste Special option comes in handy when importing date text from outside sources:

1. Copy the text date cells from the external data

2. Click a new blank cell and go to Home > Paste > Paste Special

3. Pick Date under Paste and select a date format under Operation

4. Click OK to paste the converted date values into Excel

This automates the conversion process when moving external data into Excel.

Use Flash Fill to Extract Date Values

For text strings containing both dates and other text, use Flash Fill to extract only the date portions:

1. Select the column with combined text and dates

2. Go to Data > Flash Fill and Flash Fill will intelligently detect just the dates

3. Delete the remaining text strings in the preview

4. Confirm Flash Fill to populate the extracted dates down the column

Flash Fill makes converting dates in mixed text a breeze.

Text to Columns Wizard to Split Date and Text

The Text to Columns wizard is another way to slice date values out of combined text strings:

1. Select the data and navigate to Data > Text to Columns
2. Choose Delimited > Next and check Date under Column data format
3. Click Finish to split dates into their own columns

This method helps extract dates out into their own cells when needed.

Convert Text Dates Using Replace

If you have limited text date entries, use the manual Replace method:

1. Select the cells containing text dates
2. Press Ctrl+H to open the Replace dialog
3. Enter the text date in Find what and the corrected date format in Replace with
4. Click Replace All

This replaces text strings with formatted dates in one step.

When Text Dates Don’t Change to Valid Dates

Sometimes text strings that appear like dates refuse to convert to proper Excel dates. Here are some common reasons:

• Invalid dates like 45/65/2022 can’t be directly converted and need fixing first

• Ambiguous dates like 02/04 need the full 4-digit year to convert accurately

• Text values with extra symbols or non-date text can’t change directly to dates

• Cells formatted explicitly as Text override any date formats applied

• Date conversions may fail in protected worksheets or formatted tables

• Severely corrupted Excel files may prevent successful text to date conversion

With some attention to these aspects, your text dates can be reformatted into dates quickly.

Convert Text into Dates in Bulk

Working with large datasets can make converting each text date individually impractical. Here are two approaches to automate bulk conversion:

Use Power Query

Excel’s data transformation Power Query add-in enables bulk conversion through just a few steps:

1. Select the column containing text dates
2. Go to Data > Get & Transform > From Table/Range
3. In Power Query Editor, select the date column and click Data Type > Date
4. Close and load the updated query to Excel

Write a VBA Macro

Knowledgeable users can create a VBA macro to convert text dates across entire worksheets in one go:

Sub TextToDate()   Dim rng As Range   Set rng = ActiveSheet.UsedRange   rng.NumberFormat = "m/d/yyyy"  End Sub

Running this changes all text dates in the used range into date format instantly.

Convert Dates Stored as Text for Timely Insights

Dealing with dates trapped in incorrect text formatting can be time-consuming and messy. But armed with the right techniques, you can swiftly transform those text strings into valid Excel dates.

Whether working with a few stray cells or thousands of entries, consistently applying these text-to-date conversions ensures your data is date-ready. This allows you to derive key temporal insights, run date calculations, and visualize trends free of formatting hassles.

So save yourself hours of frustration and let Excel empower you to wrangle those dates into shape fast!