Excel 2003 Date Problem – Edit a date and the year changes to 1905

[novice0902]

20 Oct 2009

Excel 2003 Date Problem – Edit a date and the year changes to 1905

My copy of Excel 2003 has developed a problem with dates. Reinstalls of Win XP and Office 2003 have not fixed the problem except temporarily.

I do the following:

1. Open Excel 2003 and enter 37000 (for example) in a cell.

2. Format > cells > date, and choose the dd.mm.yy format.

3. This converts the 37000 in the cell to 19.04.01.

At this point there is the first sign of trouble. The date is displayed in the format yyyymmdd in the formula bar but empty squares appear between the dd,mm, yyyy parts of the date. These empty squares are not blanks but actual squares. When I see them I know the problem is back even before I edit a date.The usual date separators such as slashes or dots are absent.

If I then change any part of the date the whole date will change to a date in 1905. For example, if I change the 04 to 05 in 19.04.01 the whole date changes to 23.06.05. Whatever the change, the resulting year is always 1905 but the resulting dd and mm are not always the same although dd = 23 and mm = 06 are most frequent.

The choice of date format in step two above makes no difference to the problem.

Any help with this weird problem would be appreciated.

- Novice

[jaclaz]

It's a really strange one.

Obviously it is connected with the separator used, but never saw something like that.

A "square" is how Excel usually represents some ASCII unprintable characters.

Maybe you have some strange setting in "International".

Try this test.

Open a new worksheet, enter in A1 37000, change to date Format (Save it while it is "OK", say to 37000_OK.xls)

Open a new worksheet, enter in A1 37000, change the date Format and fiddle with it until the problem occurs (Save it as soon as the "squares" appear, say to 37000_Bad.xls)

Make a copy of them.

Get Spread32.exe:

http://www.byedesign.co.uk/

and try opening each file, and see what happens.

This should clear if the change/problem is in the actual file or is in some "local" settings of "your" Excel Install.

jaclaz

[novice0902]

jaclaz,

Thanks for the advice. I will try it out and report back.

This "1905 problem" seems to be related to a particular computer. When I take any Excel 2003 worksheet that is affected by the 1905 problem on this particular computer and edit it on other computers then the 1905 problem is absent.

I believe 1 Jan 1900 and and 1 Jan 1904 have special significance in Win XP and MS Applications as start dates for counting days but I have never heard of any similar significance for 1905. I can't relate this particular year to anything I know of in MS software and can't guess why 1905 pops up.

- Novice

Edited October 22, 2009 by novice0902

[spacesurfer]

jaclaz,

I believe 1 Jan 1900 and and 1 Jan 1904 have special significance in Win XP and MS Applications as start dates for counting days but I have never heard of any similar significance for 1905. I can't relate this particular year to anything I know of in MS software and can't guess why 1905 pops up.

- Novice

No, no!! 19.04.01 is not Jan 1 1904. Nor is it in format yyyymmdd. Here yyyy would give you a 4 digit year, however, you are only seeing two digits for mm, dd, and yy, and are confusing the numbers. 19.01.04 is actually April 19, 2001 when I type it in my computer (which is ddmmyy).

I, however, cannot reproduce your problem because I don't have the format 19.04.01. My formats are with "/" as in 4/19/01, which is April 19, 2001. You need to include a screenshot so we see what you see.

[novice0902]

Excel 2003 "1905 Date Problem" Solved

jaclaz.

In your post of 20 Oct you said :

A "square" is how Excel usually represents some ASCII unprintable characters.

Maybe you have some strange setting in "International".

I decided to check Win XP >control panel >regional and language options > customise > date > date separator. There was an invalid character in the date separator box instead of the blank I thought was there. The invalid character was invisible but Win XP told me it was there when I tried to close the date tab. I then deleted this invalid character and inserted a blank which is my preferred date separator. This has solved the 1905 date problem in my Excel 2003 worksheet.

Thanks for your help.

- Novice

[novice0902]

Clarification of Jan 1 1904 Reference

Spacesurfer,

You wrote in your post:

No, no!! 19.04.01 is not Jan 1 1904.

It is just a coincidence that my example date (dd.mm.yy) of 19.04.01 is roughly like Jan 1 1904. When I mentioned Jan 1 1904 I was not referring to my example date.

- Novice

Edited October 23, 2009 by novice0902

[jaclaz]

My bad , I "translated" back my italian "internazionale" to "international", it was meant as "Regional".

Happy problem is solved.

jaclaz