formating dates

  • Thread starter Thread starter mike c
  • Start date Start date
M

mike c

i am new here so forgive me if this is an old or stupid question. i
have excel 2000 and i am trying to enter in dates for a check book
register. i set the format for the cell to date to xx/xx/xx . when i
enter in a date of 010203 for jan 2nd 2003 i get a date of 01/14/55 i
can not figure this out. i am sure i am doing something wrong since i
do not use this that often. please any help is greatly appreciated
thank you mike c
 
Mike,

You need to enter the date as 01/02/03, so that Excel will then recognise it
as a date. Otherwise it treats it as a serial number, and calculates the
date as that number of days (20,103, US style) since Jan 1 1900, which is 14
Jan 1955.

You can just enter 01/02, Excel will assume this year.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Mike if you enter your dates as if you were writing instead of typing the
problem disappears. Example: type NO SPACE 2jan2003 and your problem goes
away. with your example Excel assumes that it is 10,203 days after the 1st
Jan 1900.

HTH
for more on dates go to www.cpearson.com topic index - dates
Gilles Desjardins
 
Hi Mike!

Here's some general advice I've put together regarding date entry to
go with
the references to:

Chip Pearson:
http://www.cpearson.com/excel/DateTimeEntry.htm
and:
Dave McRitchie:
http://www.mvps.org/dmcritchie/excel/datetime.htm

Those pages both have great links at the bottom to their main topics
pages and are well worth following and then bookmarking as favorites.



Excel records dates as the number of days since 31-Dec-1899 (i.e. 1 =
1-Jan-1900)

Thus your entry of 010203 represents 10,203 days since 31-Dec-1899
which is 14-Jan-1955

To be certain that a date is what you want, you must enter a date in a
form recognised by Excel as a date. This varies from Regional setting
to Regional setting. Your 10/01/03 will be 1-Oct-2003, or 10-Jan-2003
or 03 Jan 2010 depending on my Regional settings. If some goon has
played with the double digit year interpretation you could add 20th
century options as well.

There are nine date entry forms that are unequivocally accepted by
Excel as meaning what an English language speaking user of English
language Excel Versions / Settings requires:

12 January 2002
12-January-2002
12/January/2002
12 Jan 2002
12-Jan-2002
12/Jan/2002
2002-01-12
2002/01/12
2002/1/12

Methods 3,4 and 5 are the most commonly selected. They are quick to
type and take up the least width of cell.

Method 7 is ISO8601:2000 approved (separated) form and has the even
greater advantage that it does not "demand" an English language date.
I believe it is a form that is recognized by all Regional settings but
I haven't checked that out for all countries and all Excel Versions
(OK for Versions down to Excel 97). For other forms the short or long
month names need translating to the appropriate language. Methods 8
and 9 are non-compliant ISO ordered and I would ask, "Why use these
when the real McCoy is available?"

You might note that all use 4 digit years and thus protect against
change of the double digit year interpretation setting. If you use
double digit years you risk major error if some darn fool changes your
double digit year interpretation setting or if your application is
used on another computer with different settings.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top