00-jan-00

[Gail]

Sometimes and only some worksheets even within the same workbook??? trying to
enter dates and get this result. checked tools ; Options confirmed using
1900 NOT 1904. don't recall ever mucking around with that. This issue first
started after Y2K changeover. Had a KB article on it at one time but can't
find it now. A search on the Microsoft KB produces nothing. This is driving
me crazy and that's not far to go. Can anyone tell me how to clear this up
once and for all????

 

[Dave Peterson]

I think you have an old Lotus transition setting turned on.

Tools|options|transition tab
uncheck "transition formula entry"

when you enter 01/01/2004 with this setting checked, excel figures you want to
do division:

01 divided by 01 divided by 2004.
which evaluates to: 0.000499002
and formatted as a date, you get 00-Jan-00.

(I've turned off all those transition settings.)

If that's not it, are you entering your dates with a leading equal sign:
=01/01/2004

That means to divide, too.

 

[Ken Wright]

I think you nailed it Dave - I asked the OP what was returned if formatted as
number and decimals shown and the anwer for a date entry of 11/05/2004 was
0.001097804, which probably isn't coincidence that it matches 11 / 5 / 2004

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I think you have an old Lotus transition setting turned on.

Tools|options|transition tab
uncheck "transition formula entry"

when you enter 01/01/2004 with this setting checked, excel figures you want to
do division:

01 divided by 01 divided by 2004.
which evaluates to: 0.000499002
and formatted as a date, you get 00-Jan-00.

(I've turned off all those transition settings.)

If that's not it, are you entering your dates with a leading equal sign:
=01/01/2004

That means to divide, too.

 

[Paulw2k]

Hi Gail,

I guess you are talking about the 2-digit year interpretation.

Looking in the help files, will help. Here's the extract.


About dates and date systems
How Excel interprets two-digit years

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 98 or Microsoft Windows 2000

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

For Windows NT Workstation 4.0 or dates entered as text values

When you enter a two-digit year value in Windows NT Workstation 4.0 or 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.


Hope this helps

Paul

 

[Gail]

That was teh problem and I am ever so grateful for your reply!

I think you have an old Lotus transition setting turned on.

Tools|options|transition tab
uncheck "transition formula entry"

when you enter 01/01/2004 with this setting checked, excel figures you want to
do division:

01 divided by 01 divided by 2004.
which evaluates to: 0.000499002
and formatted as a date, you get 00-Jan-00.

(I've turned off all those transition settings.)

If that's not it, are you entering your dates with a leading equal sign:
=01/01/2004

That means to divide, too.

 

[Gail]

thank you for your assistance

Hi Gail,

I guess you are talking about the 2-digit year interpretation.

Looking in the help files, will help. Here's the extract.


About dates and date systems
How Excel interprets two-digit years

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 98 or Microsoft Windows 2000

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

For Windows NT Workstation 4.0 or dates entered as text values

When you enter a two-digit year value in Windows NT Workstation 4.0 or 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.


Hope this helps

Paul