A customer sent through a spreadsheet with what appeared to be dates displayed in column A in the format YYYY-MM-DD. The spreadsheet was the result of a data export from a bespoke system and the “dates” were actually text entries.
The customer wanted the dates to be converted to DD/MM/YYYY format. One way to do this is to use the =DATEVALUE function. However, another way is to use Text to Columns.
Select the cells containing the “dates”
Select Text To Columns. You’ll find this on the DATA tab/menu, depending on which version of Excel you are using.
Ensure that the original data type is set to Delimited and click Next
Ensure that all the checkboxes are clear and click Next
Set the Column Data Format to DMY and select a destination cell. In my example the original data is in column A so I selected C1, that way I can compare the original and the new. Click Finish
You may need to widen column C but what you now have in column C are real dates in the correct format.
UPDATE: 2nd February 2014: here’s a short video:
Its Not working In my excel sheet I have a dd-mm-yy format I am try to convert it in to MM-DD-YY format but its not working
Sunil, watch the video I just added – it should help you.
Mike
excellent presentation
Hi Mike,
I’m having the same issue as Sunil; when I follow your steps to convert Text to Columns, the results are still output as text.
I’m pasting dates in MDY format from a table sent to me by email. The dates do not include leading 0’s for single-digits days and months (e.g. the source data is 9/4/2015 not 09/04/2015 for 4 September, 2015). Where the Day value is less than 12, Excel is converting the date automatically and incorrectly (eg 9/4/2015 is converted to 9 April instead of 4 September). The Text to Columns wizard returns these dates with no changes and returns still as text those dates where Day is greater than 12.
The default language and locale of my Excel is French. Any other ideas?
Thanks!
Hi , in my case the date is not separated with slash . it is like : 20160328
What should I do ?
@Nabeel what version of Excel are you using? If you are using 2013 or later on Windows, Flash Fill would be easier than Text to Columns.
Hi , thanks . I have Excel 2016 . At work , I have excel 2010 . I tried text to column in both but did not work . What I see in this video is 2016-03-04 while in my case, I am downloading the CSV file from the bank website and it shows date as 20160304 . I also have no idea what is flash fill . Thanks Nabeel
@Nabeel, try again using the steps in this tutorial but as the last step use YMD (assuming the date in your CSV is YMD which I think it is – in your first comment you say it is 20160328
You are a WONDERFUL assist ! After spending weeks and weeks , it is in fact the YMD that solved the problem because the bank produced the data as 20160328. Many thanks , really great
Mike Thomas has the correct solution to the trick not working: the date formats in the original column and the wizard have to match. If your text dates are YMD, then you MUST specify YMD in the wizard or it won’t work. These must match, no matter what your target column’s formatting is.
Excellent! Worked perfectly fine.
In step 3 of the wizard, when you select Date and select DMY or MDY from the dropdown, this is telling Excel how the source data is organized, NOT how you want to see the results.
The results are always Excel dates which you can format as you wish.
Excel needs to know this because dates such as 6/5/2018 can be interpreted in 2 ways; your choice of DMY or MDY tells Excel which to use.