# Convert Number to Date

#### rwmill9716

##### Active Member
What is the formula to convert a number like 122114 to 12/21/14 and 13015 to 01/30/15?

Thanks

Ric

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### AlphaFrog

##### MrExcel MVP
What is the formula to convert a number like 122114 to 12/21/14 and 13015 to 01/30/15?

Thanks

Ric

Try this...
=TEXT(A1,"00-00-00")*1
Format the formula cells as Date.

This assumes the "day" value always has a two digits (a leading zero for single digit days). Otherwise it doesn't know if
12915 is 1/29/2015 or 12/9/2015

#### Trouttrap2

##### Well-known Member
try this
=TEXT(A1,"00\/00\/00")

#### azumi

##### Well-known Member
=DATEVALUE(MID(A1,3,2)&"/"&LEFT(A1,2)&"/"&(RIGHT(A1,2)))
and formatted as date

The second date could be problem when have 5 digits, try to add apostrophe " ' " when you have 01 or 02 and so on

#### SpillerBD

##### Well-known Member
Accounting for the possibility of preceding zeroes....
=(LEFT(B1,LEN(B1)-4)&"/"&MID(B1,LEN(B1)-3,2)&"/"&RIGHT(B1,2))

#### rwmill9716

##### Active Member
Thanks, Trouttrap2 and AlphaFrog, these both work fine.

Ric

#### SpillerBD

##### Well-known Member
Accounting for the possibility of preceding zeroes....
=(LEFT(B1,LEN(B1)-4)&"/"&MID(B1,LEN(B1)-3,2)&"/"&RIGHT(B1,2))

oops.. that should be
=DATEVALUE(LEFT(B1,LEN(B1)-4)&"/"&MID(B1,LEN(B1)-3,2)&"/"&RIGHT(B1,2))

Replies
1
Views
249
Replies
4
Views
168
Replies
3
Views
86
Replies
3
Views
255
Replies
4
Views
646

1,195,905
Messages
6,012,218
Members
441,682
Latest member

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