In Excel 2003, entering date without slashes, the date is incorre.

[sj]

enter date 010305 and the date changes to 3/18/28 very strange and can be
repeated. Is there a fix for this? Running excel 2003, 11.6113.5703

[Jim Rech]

You have to use a date separator for Excel to know what you're entering.
Dates are in reality just serial numbers starting with 1/1/1900 as day 1.
Day 10305 is evidently 3/18/1928.

--
Jim Rech
Excel MVP
"sj" <sj@discussions.microsoft.com> wrote in message
news:28CDFBBE-E343-461D-87ED-9D111C5B2244@microsoft.com...
| enter date 010305 and the date changes to 3/18/28 very strange and can be
| repeated. Is there a fix for this? Running excel 2003, 11.6113.5703

[Bob Phillips]

If you have a need for a lot of such dates, you could try the QDE addin, to
be found at http://www.xldynamic.com/source/xld.QDEDownload.html

--

HTH

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


"sj" <sj@discussions.microsoft.com> wrote in message
news:28CDFBBE-E343-461D-87ED-9D111C5B2244@microsoft.com...
> enter date 010305 and the date changes to 3/18/28 very strange and can be
> repeated. Is there a fix for this? Running excel 2003, 11.6113.5703

[sj]

my client says this worked in Office 97 and he formatted the cells to be date
and then just entered numbers like 010505 and it set the formatting
correctly-any other idea?

"sj" wrote:

> enter date 010305 and the date changes to 3/18/28 very strange and can be
> repeated. Is there a fix for this? Running excel 2003, 11.6113.5703

[Jim Rech]

>>my client says this worked in Office 97 and he formatted the cells to be
>>date
and then just entered numbers like 010505 and it set the formatting
correctly-any other idea?

Your client, ah, misremembers I think. Excel 97 and 2003 are the same in
this regard. Cell formatting affects how a value in a cell is displayed,
not how Excel interprets it at entry time. A macro of course can perform
all sorts of transformations so maybe that was involved.

--
Jim Rech
Excel MVP
"sj" <sj@discussions.microsoft.com> wrote in message
news:FFDA47D4-79F6-4D20-A150-B3C6BD19D02F@microsoft.com...
| my client says this worked in Office 97 and he formatted the cells to be
date
| and then just entered numbers like 010505 and it set the formatting
| correctly-any other idea?
|
| "sj" wrote:
|
| > enter date 010305 and the date changes to 3/18/28 very strange and can
be
| > repeated. Is there a fix for this? Running excel 2003, 11.6113.5703

[Earl Kiosterud]

sj,

It couldn't have. Dates worked the same in Excel97 as in more recent
versions. It's seeing 010305 as the number 10,305, which is the date-serial
number for the date 3/18/28. You must use some kind of date separator for
it to see it as 01/03/05. Consider the add-in that Bob suggested. It will
allow entry as you want, and will convert it to the intended date.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"sj" <sj@discussions.microsoft.com> wrote in message
news:FFDA47D4-79F6-4D20-A150-B3C6BD19D02F@microsoft.com...
> my client says this worked in Office 97 and he formatted the cells to be
> date
> and then just entered numbers like 010505 and it set the formatting
> correctly-any other idea?
>
> "sj" wrote:
>
>> enter date 010305 and the date changes to 3/18/28 very strange and can be
>> repeated. Is there a fix for this? Running excel 2003, 11.6113.5703

[Don Guillett]

try
right click sheet tab>view code>copy/paste this>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.row < 2 Or Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
x = Target
Target = Left(x, 2) & "/" & Mid(x, 3, 2) & "/" & Right(x, 2)
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
"sj" <sj@discussions.microsoft.com> wrote in message
news:FFDA47D4-79F6-4D20-A150-B3C6BD19D02F@microsoft.com...
> my client says this worked in Office 97 and he formatted the cells to be
date
> and then just entered numbers like 010505 and it set the formatting
> correctly-any other idea?
>
> "sj" wrote:
>
> > enter date 010305 and the date changes to 3/18/28 very strange and can
be
> > repeated. Is there a fix for this? Running excel 2003, 11.6113.5703

[daniels012]

I am not sure what you are wanting to accomplish by entering the dates that way, but you can right click the cell click on... Format Cells then left under catergory pick... Custom then on the right click in the Type: area and enter this exactly like the following 00"/"00"/"## This will give you a display you want. 02/05/05
if you don't want the date displayed this way, then you can format it like the following 0000## This will give you 020505

Hope this helps

Michael