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

[Guest]

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.

 

[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)

 

[Guest]

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?

 

[Jim Rech]

my client says this worked in Office 97 and he formatted the cells to beand 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
| 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.

 

[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