I have a bunch of numbers on an excel file that are this format: 70220. I
would like to convert them to a regular date. I tried just formatting the
cells to dates, but it does not work. It should be 2/20/07. Can anyone help?

Thanks.

=DATE(2000+LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-3,2),RIGHT(A1,2))
--
David Biddulph

"Jacq" wrote in message
...
I have a bunch of numbers on an excel file that are this format: 70220. I
would like to convert them to a regular date. I tried just formatting the
cells to dates, but it does not work. It should be 2/20/07. Can anyone
help?

Thanks.

Assuming all your dates are 2000 or later

=TEXT(20&TEXT(A1,"000000"),"0000-00-00")+0

format as date

"David Biddulph" wrote:

=DATE(2000+LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-3,2),RIGHT(A1,2))
--
David Biddulph

"Jacq" wrote in message
...
I have a bunch of numbers on an excel file that are this format: 70220. I
would like to convert them to a regular date. I tried just formatting the
cells to dates, but it does not work. It should be 2/20/07. Can anyone
help?

Thanks.

=TEXT(200&A1,"0000\/00\/00")*1


"daddylonglegs" wrote:

Assuming all your dates are 2000 or later

=TEXT(20&TEXT(A1,"000000"),"0000-00-00")+0

format as date

"David Biddulph" wrote:

=DATE(2000+LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-3,2),RIGHT(A1,2))
--
David Biddulph

"Jacq" wrote in message
...
I have a bunch of numbers on an excel file that are this format: 70220. I
would like to convert them to a regular date. I tried just formatting the
cells to dates, but it does not work. It should be 2/20/07. Can anyone
help?

Thanks.

Try this:

=DATE(LEFT(A1,1)+2000,MID(A1,2,2),RIGHT(A1,2))


"Jacq" wrote:

I have a bunch of numbers on an excel file that are this format: 70220. I
would like to convert them to a regular date. I tried just formatting the
cells to dates, but it does not work. It should be 2/20/07. Can anyone help?

Thanks.

Thank you all so much. Very helpful. It finally works.

"Jacq" wrote:

I have a bunch of numbers on an excel file that are this format: 70220. I
would like to convert them to a regular date. I tried just formatting the
cells to dates, but it does not work. It should be 2/20/07. Can anyone help?

Thanks.