Hand entered dates view wrong

[Chris in Nebraska]

A user I support is having an issue with a worksheet in Excel. It is
not an urgent or crucial fix, but I am interested in people's ideas,
since she says it has happened to her before. In each case she threw
away the worksheet and started a new one by hand entering the data again.

Here's the scenario: She has a worksheet in which she has been adding
rows of data over time (6 months or so) and has only about 30 rows of
data. Column A are dates, and are formatted as such.

After months of opening and saving in this document once a week or so,
she now has an issue - when she types in a date, it gives her bizarre
dates from the future. For example, when she enters 8/01/05 by hand,
the date displayed in the cell is 4/26/2119.

Things I know:

The 1904 date system is not checked in Tools/Options/Calculations

The worksheet works fine on my computer when she emails it to me

We have cleared the formatting on the cell, and the entire column

The local system time is correct



Has anyone seen such issues?

- Chris in Nebraska

 

[Anne Troy]

What happens if she uses the format painter and copies the format from a
good formatted cell to these cells?
http://www.officearticles.com/misc/format_painter_in_microsoft_office.htm
*******************
~Anne Troy

www.OfficeArticles.com

 

[Earl Kiosterud]

Chris,

I wonder if we looked at the underlying value, might we get a clue. Try
removing the date formatting (Edit - Clear - Formats will do it -- you can
undo it after you've seen the underlying date serial number).

 

[Dave]

Might not help, but try comparing the date formats in her regional
settings with yours. Control Panel>Regional Options, click customize,
choose Date tab, see what the setting is for dates entered as two digit
years. I say this because you indicate the year is entered as two
digits, poor practice for MS Office ever since Y2K. Regardless of the
numeric (not text) date format, years should be entered as four digits
(i.e. even if format is mm/dd/yy, enter 08/03/2005). She should try her
sheet with four digit years if she hasn't already done so.
If none of that gives you a clue, change the format in her spreadsheet
on her machine and on yours to General. The number you get on each for
a given date should be the same (08/01/2005 is 38565). If you don't get
the same result, and the system clocks are correct on both, I have to
suspect the regional settings are the cause.

 

[Ron Rosenfeld]

After months of opening and saving in this document once a week or so,
she now has an issue - when she types in a date, it gives her bizarre
dates from the future. For example, when she enters 8/01/05 by hand,
the date displayed in the cell is 4/26/2119.

It seems to me she is forgetting to put in the date separators.

The serial number 080105, when entered into a date formatted cell, will display
4/26/2119.

Another option is that someone programmed her auto correct options to eliminate
the "/" but that's less likely.


--ron

 

[Chris in Nebraska]

A-HA!! I knew someone would have the answer!

Many thanks Ron - I will let her know!

 

[Ron Rosenfeld]

A-HA!! I knew someone would have the answer!

Many thanks Ron - I will let her know!

You're welcome. Hopefully the answer is correct :-|


--ron