Date Function Problem

[RCampbell]

I've been trying to use the date function to format 010103
as 01/01/03 but it is changing the date to 08/29/27. Any
ideas?

 

[Peo Sjoblom]

You can't use it that way, excel dates are serial numbers where the first
date is January 0 1900,
so if you enter 010103 it will mean 10103 days after Jan 0 1900, excel will
not differ
between the number 10,103 and the date 10103. Formatting will not change the
underlying value.
You can use VBA if you don't want to enter the date delimiter

http://www.cpearson.com/excel/DateTimeEntry.htm

 

[Peter Atherton]

-----Original Message-----
I've been trying to use the date function to format 010103
as 01/01/03 but it is changing the date to 08/29/27. Any
ideas?
.

if your date is in A26
=DATE(RIGHT(A26,2),MID(A26,2,2),LEFT(A26,2))

Peter