Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Error Bars in Excel
  • How to create a Histogram in Excel
  • Chart Axes in Excel
  • Excel Bar Chart
  • How To Sort One Column or Multiple Columns in Excel

References

  • How to get last row in numeric data in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Vlookup Examples in Excel
  • How to retrieve first match between two ranges in Excel
  • Two-way lookup with VLOOKUP in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation whole percentage only
  • Excel Data validation with conditional list
  • Prevent invalid data entering in specific cells
  • Excel Data validation don’t exceed total

Convert text to date in Excel

by

This tutorial show how to Convert text to date in Excel using the example below.

To convert text in an unrecognized date format to a proper Excel date, you can parse the text and assemble a proper date with a formula based on several functions: DATE, LEFT, MID, and RIGHT.

Note: Before you go the formula route, see below for other ways you might be able to get Excel to recognize text as dates without formulas.

Formula

=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))

Explanation of how this formula works

 

In the example shown, the formula in C6 is:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

This formula extract the year, month, and day values separately, and uses the DATE function to assemble them into the date October 24, 2000.

Background

When you’re working with data from another system, you might run into text values that represent dates, but are not understood as dates in Excel. For example, you might have text values like this:

Text Date represented
20001024 October 24, 2000
20050701 July 1, 20115
19980424 April 24, 1998
28.02.2014 February 28, 2014

Excel won’t recognize these text values as a date, so in order to create a proper date, you’ll need parse the text into it’s components (year, month, day) and use these to make a date with the DATE function.

The DATE function takes three arguments: year, month, and day. The LEFT function extracts the leftmost 4 characters and supplies this to DATE as the year. The MID function extracts characters 5-6 and supplies this to DATE as the month, and the RIGHT function extracts the rightmost 2 characters and supplies these to DATE as the day. The final result is a proper Excel date that can be formatted any way you like.

Another example

In row 8, the (unrecognized) date format is dd.mm.yyyy and the formula in C8 is:

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

Text to columns to fix dates

Another way to get Excel to recognize dates is to use the Text to Columns Feature:

Select the column of dates, then try Data > Text to columns > Fixed > finish

This can sometimes fix everything at once.

Add zero to fix dates

Sometimes, you’ll encounter dates in a text format that Excel should recognize. In this case, you might be able to force Excel to convert the text values into dates by adding zero to the value. When you add zero, Excel will try to coerce text values to numbers. Since dates are just numbers, this trick is a great way to convert dates in text format that Excel really should understand.

To convert dates in place by adding zero, try Paste Special:

  1. Add a zero to an unused cell and copy to the clipboard
  2. Select the problematic dates
  3. Paste Special > Values > Add

To convert dates by adding zero in a formula, use:

=A1+0

where A1 contains an unrecognized date.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel AND Function
  • Check multiple cells are equal in Excel
  • How to use Excel TRUE Function
  • How to return blank in place of #DIV/0! error in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • Count times in a specific range in Excel
  • How to calculate percent of year complete in Excel
  • How to calculate most recent day of week in Excel
  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • How to randomly assign people to groups in Excel
  • Group times into 3 hour buckets in Excel
  • How to randomly assign data to groups in Excel
  • Categorize text with keywords in Excel
  • Calculate conditional mode with criteria in Excel

General

  • How to test a range for numbers in Excel
  • How to password protect excel sheet?
  • Find, Select, Replace and Go To Special in Excel
  • List sheet names with formula in Excel
  • 3D SUMIF for multiple worksheets in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning