Problem with date format

  • Thread starter Thread starter Colin
  • Start date Start date
C

Colin

I set the UK date format fine, or appeared to.

Then, when I type in 250104 to bring about a date format of 25/01/04 but
after typing the date the cell reads 03/10/84.

I was always under the impression that if I typed in 250104 it would accept
it and then proceed to alter to what ever date format was chosen be it
25/10/04 or 25th October 2004?

Please advise.
Many thanks
Colin
 
after typing the date the cell reads 03/10/84.

If you look in the formula bar, it's actually 3rd October 2584, since that's
the 250,104th day after 1st Jan 1900.

You need to use a separator, either "-" or "/". You can use 25-10-04 or
25/10/04, followed by Enter.

If it's any consolation, 25/1/4 will do for the next 25 or so years. If
you've preformatted the cells, then 25/1 followed by Enter will add on the
current year. So that's only four keystrokes.

HTH,
Andy
 
Don't know what gave you that impression, but it's never been true in
XL. How would XL know that 250104 was a date rather than a part number
or your annual salary? The input parser and the display engine are
separate entities.

See Chip Pearson's site for a macro you can adapt to do what you want:

http://cpearson.com/excel/DateTimeEntry.htm
 
Hi JE!

More for discussion than otherwise.

Re: How would XL know that 250104 was a date rather than a part number
or your annual salary?

That's correct because Excel uses a date serial number system. If they
had used a separate data type for dates, then it might have been
possible. Even then, if a cell has been pre-formatted to receive
dates, I wonder if Excel's intellisence might have worked to accept it
as a date.

--
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.
 
Hi Colin!

You could enter in that form and then use the following conversion
formula provided dates are all this century:

=DATE(RIGHT((A1),2)+2000,RIGHT(INT(A1/100),2),INT(A1/10000))

If you have dates before 2000 then you need to use:

=DATE(IF(--RIGHT(A1,2)<30,RIGHT((A1),2)+2000,RIGHT(A1,2)+1900),RIGHT(I
NT(A1/100),2),INT(A1/10000))
Assumes years below 30 are this century but years 30+ are last century
(i.e. default double digit year interpretation)

Chip Pearson does have a quick entry date macro that uses a
worksheet_change event on the range used but that is for entry using
the US system of mmddyy

--
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.
 
Norman Harker said:
More for discussion than otherwise.

Re: How would XL know that 250104 was a date rather than a part number
or your annual salary?

That's correct because Excel uses a date serial number system. If they
had used a separate data type for dates, then it might have been
possible. Even then, if a cell has been pre-formatted to receive
dates, I wonder if Excel's intellisence might have worked to accept it
as a date.
Click to expand...

I don't think the data type has as much to do with it as the plethora of
date formats.

While the parser and the display engine are entirely separate, there are
two instances where the Editor affects how entries are parsed:

1) When the cell is formatted as Text, entries are interpreted as text,
not parsed as numeric.

2) When the cell is formatted as Percentage, the input editor adds "%"
to the end of the entry so that, unless it's explicitly deleted, the
parser will then interpret the entry as a percentage.

In principle, I can't think of a reason that a cell formatted as a date
can't be similarly handled by the input editor, which would hand off to
the parser something that looked like a date. However, given the fact
that dates can be entered with two different separators, possibly with a
time (with its own separator), and that months may be numeric or
alphabetic (in multiple languages), I suspect the problem was considered
too complex when XL was designed. Entering dates without separators
would be even more complex - e.g., 111 being one of 1 January 2001, 11
January 2004, 1 November 2004, etc.

Now, of course, I suspect the bigger problem is one of compatibility -
not breaking the tens of millions of working spreadsheets out in the
wild.
 
Hi JE!

Thanks! It's a useful discussion. Actually there are three separators
allowed at present irrespective of Regional Setting variations; the
third is a simple space.

It's like a lot of things. Once built into the program, it's difficult
to change if backwards compatibility is to be retained.

--
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.
 
Norman Harker said:
Thanks! It's a useful discussion. Actually there are three separators
allowed at present irrespective of Regional Setting variations; the
third is a simple space.
Click to expand...

Spaces only when the month is text, and even then perhaps subject to
regional settings. With standard US settings,

1 Jan 2004 is interpreted as a date, but
Jan 1 2004 is interpreted as text, and so are
1 1 2004 and 2004 1 1 while
1-1-2004 and 2004-1-1 are both interpreted as dates
Jan 1, 2004 is also interpreted as a date.
It's like a lot of things. Once built into the program, it's difficult
to change if backwards compatibility is to be retained.
Click to expand...

Backward compatibility in formulas is something for which backward
compatibility is very valuable if not necessary. Backward compatibility in
constant entry semantics is optional as long as entries once entered and
interpreted and the containing workbook saved would be evaluated the same in
other versions. In other words, entry semantics require as little backward
compatibility as the = button in the formula bar: at most an annoyance
flipping back and forth between versions, with no effect once entries have
been made.

What gets me is Microsoft's inability (or indifference) thoroughly to
integrate the various Office applications. Excel with Word's extended search
and replace wildcards and Access's input masks would be quite something.
 
Thank you all. It seems I really need to simply accept that if I must use a
/ or - between the d m and year. Don't know why I got impression I did,
perhaps I've just seen it happen in other products. Thanks again.
 
Hi Colin!

If you get used to entry of dd-mmm-yyyy or dd/mmm/yyyy you'll never
hit problems with any English language version or Regional Settings of
Country or double digit interpretation. If you format that way as a
norm, you'll not confuse any user who is accustomed to different date
representation regimes.

If you want to be truly International then it's yyyy-mm-dd (but that
takes a lot of getting used to!)
--
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