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),R IGHT(D13,2))

--

HTH

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


"Jason Morin" wrote in message
...
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
.