Excel date format

Question

I have an excel file with dates in the following format: 20120529

I could change it manually to 29-05-2012, but it will take a long time since I have more than five thousand rows, is there any way to change the date format automatically for every row?

Answer 1

I assume you're dealing with a string/number. If it's a date, simply format the date to dd-mm-yyyy.

Otherwise, you could break it down using the string manipulation functions mid, left and right:

=(right(A1,2)&"/"&mid(A1,5,2)&"/"&left(A1,4))*1

Then format as dd-mm-yyyy through 'Format Cells'. I believe there might be some issues if the regional settings are in US instead of UK. If it's the former and you do get issues with the first formula, I would use:

=(mid(A1,5,2)&"/"&right(A1,2)&"/"&left(A1,4))*1

Then format as dd-mm-yyyy.

Answer 2

of course you can!

Use the date function

DATE(year,month,day)

Assuming your date in YYYYMMDD is in A1, put this in B1:

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