Date Formatting having no effect - MS Excel 2000 1

[cdck]

I have a user who was working with a spreadsheet that has been in use for many months and encountered a glitch. One column in the spreadsheet holds date values. This column is formatted to show the date as "03-March" even though input as any other date format, such as "3/3/09".

The sheet worked correctly until last week, when suddenly it started showing dates in updated fields in whatever format the update was done in. Fields that are not changed remain in the correct format. All attempts to re-format these fields have no affect on the cell output.

The same spreadsheet opened on another computer will function as expected once the formatting is reset.

Also, we have since learned that all spreadsheets this user has malfunction in the same way for her now. Formulas still function correctly; the only issue seems to be the date.

It would appear that something happened to some sort of system setting, but I am unable to find one. Has anybody seen this issue before?

Cheryl dc Kern

 

[SkipVought]

Hi,

If something was PASTED into the date field, the cell might now contain some other format. That's how Copy 'n' Paste works, unless your PasteSpecial-VALUES.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[cdck]

No, nothing was pasted. The user is just trying to type a new value over the old one. But even if a new format had been pasted, it should be capable of being reset when you reformat the cell, but it is not.

Cheryl dc Kern

 

[SkipVought]

exactly what values is enteed and what format is selected in Format > Cells...?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

-> But even if a new format had been pasted, it should be capable of being reset when you reformat the cell, but it is not.

That's a reasonable expectation, but 'fixing' the format can be tricky. But that's beside the point since you said she's not pasting anything.

What you're describing certainly sounds like your dealing with text, not a number.

Check for leading/trailing spaces or anything else that might cause this user's entry to vary from the "MM/DD/YY" format. It could be something as simple as a sticky key on her keyboard.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

FYI,

Excel STORES dates as pure numbers, like right now, in North Texas, the Date/Time VALUE is 39903.37794, which can be FORMATTED any number of ways, like 3/31/2009 9:04.

When you enter TEXT like...
[tt]
3/3/09
[/tt]
Excel assumes that you are entering a date and CONVERTS the text into a NUMBER, that corresponds to March 3, 2009, which is 39903 days after 12/31/1899.

If you happen to enter a leading SPACE, like
[tt]
[highlight red] [/highlight]3/3/09
[/tt]
the conversion may not take place, as John previously pointed out.



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[cdck]

The format is supposed to be custom>d-mmm.

I checked the cells that have been affected, and there is no leading space.

She has entered "03/25/09". This is exactly what shows in the cell despite the formatting.

I also note that when i open the file on my machine and do anything to the field, even just click into it, move the cursor through it, and hit enter, the correct formatting is automatically applied. It's as if there is something on her machine that is keeping the formatting from being applied when it should.

Cheryl dc Kern

 

[AnotherHiggins]

I don't mean to belabor the point, but just to be perfectly clear - have you tried clicking into the cell on her machine after the date has already been entered and after the format has been changed? (either double clicking, [F2] or "move the cursor through it, and hit enter"?)

The reason I'm asking is because what you describe happening on your computer is exactly the behavior I'd expect to see with "stubborn text" - such as something that's been pasted in.

Assuming that's been done.... This almost sounds like something that could be caused if the date settings were wrong in Control Panel > Region and Language Options. But your example doesn't quite fit.

Does this happen for both "3/31/09" and "3/3/09"? If her machine was set up to "dd/mm/yy" (European style) then excel would be confused by "3/31" because it would think that you are putting "31" in for the month. But if that's the problem, then "3/3/09" shouldn't break anything since Excel wouldn't be confused by a "3" in the month position.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[cdck]

It was the date and time settings in regional settings. They had become set to military time, and though i don't understand why that would affect it, as soon as it was corrected, formatting happened correctly for her every time.

Thanks, everyone!

Cheryl dc Kern