Date conversion

I want to convert a number i.e. 041006 into a date, having
formatted the new cell as date: dd/mm/yyy. When I use
the formula =DATE(left(D13,2),MID(D13,3,2),right(D13,2)
I get the date 06/10/1904, when it should be 06/10/2004.
Any suggestions please

=DATE(IF(INT(LEFT(D13,2))>10,1900,2000)+LEFT(D13,2),MID(D13,3,2),RIGHT(D13,2))

- Mangesh

Almost. Excel is interpreting "04" as 1904. Try:

=DATE("20"&LEFT(A13,2),MID(A13,3,2),RIGHT(A13,2))

Good for dates 2000 and after.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I want to convert a number i.e. 041006 into a date,
having
>formatted the new cell as date: dd/mm/yyy. When I use
>the formula =DATE(left(D13,2),MID(D13,3,2),right(D13,2)
>I get the date 06/10/1904, when it should be 06/10/2004.
>Any suggestions please
>.
>

Hi

This is a little confusing; if you have 041006 as a real number, it should
not display the leading 0. amd your left and mid function would err. Give
this a try:

=DATE(1900+MOD(A1,100)+100*(MOD(A1,100)<50),MOD(INT(A1/100),100),INT(A1/1000
0))

The 50 means
49 = 2049
50 = 1950
change to suit your needs.

HTH. Best wishes Harald


"Gerrym" <[email protected]> skrev i melding
news:[email protected]...
> I want to convert a number i.e. 041006 into a date, having
> formatted the new cell as date: dd/mm/yyy. When I use
> the formula =DATE(left(D13,2),MID(D13,3,2),right(D13,2)
> I get the date 06/10/1904, when it should be 06/10/2004.
> Any suggestions please

You could also include a test for the 1900's. This is a technique we started
using in the 90's as the millennium approached

=DATE(IF(--(LEFT(D13,2))>70,19,20)&LEFT(D13,2),MID(D13,3,2),RIGHT(D13,2))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jason Morin" <[email protected]> wrote in message
news:[email protected]...
> Almost. Excel is interpreting "04" as 1904. Try:
>
> =DATE("20"&LEFT(A13,2),MID(A13,3,2),RIGHT(A13,2))
>
> Good for dates 2000 and after.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I want to convert a number i.e. 041006 into a date,
> having
> >formatted the new cell as date: dd/mm/yyy. When I use
> >the formula =DATE(left(D13,2),MID(D13,3,2),right(D13,2)
> >I get the date 06/10/1904, when it should be 06/10/2004.
> >Any suggestions please
> >.
> >