Convert General Number to Date Format

[heathbb]

I have a unique problem I need solved.
I need to convert a column of general numbers to a date format. I have more than 100000 rows of this format I need converted.

I start with the following general format in the column
1150621
This number equates to 2015 June 21
I want it get it into 21/06/2015 format.
I have used this formula “=20&(REPLACE(LEFT(F6,3)&"-"&MID(F6,4,2)&"-"&RIGHT(F6,2),1,1,""))”
and got the following result
2015-06-21 (which looks like a date to me)
From there I cannot get Excel to format that as a date.
I have tried to copy and paste as value and then format it, cant get it right.

I am using Excel 2010 on Windows 7 Laptop.

Please help

 

[Rick Rothstein]

Try this formula...

=0+TEXT(19000000+F6,"0000-00-00")

then format its cell with the date format you want.

 

[AlanY]

will the number say, 3141102 is 02/11/2014?

 

[heathbb]

will the number say, 3141102 is 02/11/2014?

No. The first number is always a 1.

So for 02/11/2014 my number would be 1141102.

 

[Rick Rothstein]

@heathbb... I think you may have missed what I posted in Message #2.

 

[vicedo]

Try:

=DATE(20&LEFT(REPLACE(F6,1,1,""),2),MID(F6,4,2),RIGHT(F6,2))

 

[heathbb]

@heathbb... I think you may have missed what I posted in Message #2.

Hi Rick

I did see the post, I was busy making sure it worked for all the rows of this format.

Thank you it worked very well