Change Excel data

Question

I have Excel doc that contains date column, but it was writtn as 19920504 the real date is 04-05-1992 is there any method or formula to fix it? (rather than manully because I have huge data)

What is the format of the initial data? Text, number, date??? — Alex P, Jul 15 '18 at 08:22
I didn't create that file, but when I make a new file it gives me the same result. — mona, Jul 15 '18 at 08:27
Easy to fix................is the "real" date **April 5** or **May 4** ?? — Gary's Student, Jul 15 '18 at 10:48
Thanks you all, it worked for me now. I found the answer in the link, next answer.(https://stackoverflow.com/questions/44247877/changing-yyyymmdd-to-mm-dd-yyyy) — mona, Jul 16 '18 at 06:49

score 3 · Answer 1 · answered Jul 15 '18 at 08:40

3

If H5 contains 19920504 then this formula returns an Excel date: (in the UK it returns 04/05/1992 )

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

answered Jul 15 '18 at 08:40

Charles Williams

23,121
5
38
38

score 2 · Accepted Answer · answered Jul 15 '18 at 09:21

2

Select the column with the dates in it and go to Data | Text to Columns. Skip the first couple of screens, and in the next screen set the date format to YMD.

answered Jul 15 '18 at 09:21

Tom Sharpe

30,727
5
24
37

Change Excel data

2 Answers2