Date format: how does Excel choose which century for 2-digit entries?

[Elwood P. Dowd]

I'm trying to clean up a mess that is oddly reminsicent of the whole Y2K bug
hoopla. I have a colleague who has created a large number of spreadsheets
over the last year, who "doesn't have time" to type all four digits when
entering years (so, he enters "3/23/04" instead of "3/24/2004"). He came to
me for help because some of his clients are complaining that the dates are
showing up their systems as being wrong -- 1904 instead of 2004. I have 2
questions as a result:

1. Why would dates show up differently (i.e., show all 4 digits) on other
user's systems? I would think the format settings for a cell would transfer
with the spreadsheet, regardless of any defaults on their PCs.

2. How does Excel decide what century an entry like "04" should be -- 1904
or 2004? Is there a way of creating settings to tell Excel how to handle
two-digit year entries (i.e., 00-30 should be 20##, while 31-99 should be
19##)?

TIA!

 

[Frank Kabel]

Hi
the dates shouldn't change after transfering to a different system.
they're probably stored as 1904 on the source system (but without using
a format which shows all 4 digits for a year no one has noticed)

Excel uses the settings in the Windows Regional settings ('Control
Panel - Regional settings - Date' for deciding to wheach century the 2
digit year belongs (Default should be 1930 - 2029 -> that is 04 should
be interpreted as 2004; 31 as 1931)

 

[Niek Otten]

From Excel site:
<

To ensure that year values are interpreted as you intended, type
year values as four digits (2001, rather than 01). By entering four digits
for the years, Excel won't interpret the century for you.

For Microsoft Windows 2000 or later

If you are using Microsoft Windows 2000 or later, the Regional
Options in Windows Control Panel controls how Excel interprets two-digit
years.

For dates entered as text values

When you enter a date as a text value, Excel interprets the year
as follows:

a.. 00 through 29 Excel interprets the two-digit year
values 00 through 29 as the years 2000 through 2029. For example, if you
type the date 5/28/19, Excel assumes the date is May 28, 2019.
b.. 30 through 99 Excel interprets the two-digit year
values 30 through 99 as the years 1930 through 1999. For example, if you
type the date 5/28/98, Excel assumes the date is May 28, 1998.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Elwood P. Dowd]

Thanks to both of you for such quick, helpful answers!