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?
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.
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))
