Change date format in excel from ddmmyyyy to dd/mm/yyyy

Suppose you have got some data in the format ddmmyyyy (eg. 01122011) and you want to get it in the format dd/mm/yyyy

Here I discuss one method to do it

Suppose the data (01122011) is in cell B2

 

Get the date  (lets say in C2) by using the LEFT function =LEFT(B2,LEN(B2)-6)

Get the month  (lets say in D2) by using the MID function =MID(B2,LEN(B2)-6+1,2)

Get the month  (lets say in E2) by using the RIGHT function =RIGHT(B2,4)

Now get it to date by using the DATE function =DATE(C2,D2,E2)

 

Or do it all in single step (lets say in C2) by using all of the above formulae =DATE(RIGHT(B2,4),MID(B2,LEN(B2)-6+1,2),LEFT(B2,LEN(B2)-6))

 

Now just format it to DD/MM/YYYY.