It is a smart formula capable of converting dates stored as text to excel understandable date format. Now depending on the format of source data, write one of the below formulas to convert text to dates.ĭATEVALUE formula tells excel to fetch the date from a given input.Technique 2: Using Formulas to Convert Text to Dates Your text dates are now converted to excel understandable dates. It doesn’t matter what is the format of source date, month or year is. In step 3, select “Date” and specify the format of the date – like YMD, MDY, DMY, YDM, MYD or DYM.Leave defaults or make changes in step 1 & 2.Go to the Text Import Wizard (or Text to Columns dialog).At this point, Excel will prompt you for using “Text to columns” utility (or Text Import Wizard as it is called in Excel 2007).Now copy the values from text file and paste them in Excel.First copy the source data and paste it in a text file (open Notepad and paste there).In this post, we will learn some tricks and ideas you can use to quickly convert text to dates. The separator between date, month and year is not a known separator (for eg.The dates are formatted for another country (or date system) and hence your version of Excel wont recognize them.The data has some extra spaces, other characters before and after the date values.The date format is different from one that is understood by excel.This can be due to any number of reasons, including, Sometimes when we import data from another source in to excel, the dates are not imported properly.

Excel text import wizard defaults