# Convert General Number to Date Format

#### heathbb

##### New Member
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.

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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Rick Rothstein

##### MrExcel MVP
Try this formula...

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

then format its cell with the date format you want.

#### AlanY

##### Well-known Member
will the number say, 3141102 is 02/11/2014?

#### heathbb

##### New Member
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

##### MrExcel MVP

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

#### vicedo

##### Active Member
Try:

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

#### heathbb

##### New Member
@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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,384
Messages
5,836,946
Members
430,464
Latest member
nickburrett

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back