excel format issue

  • Thread starter Thread starter Mann
  • Start date Start date
M

Mann

Hi Group,

I have a strange problem.
When i enter a date say 08/3/2004 and i goto format
cells ,under the NUMBER tab, to date and select any date
format and apply it still remains in the above format e.g
08/3/2004.
The format doesnt change.
I noticed one more things, even when selecting any date
format the instance in the SAMPLE window above the TYPE:
window remains the same e.g 08/3/2004 it doesnt change
according to the format selected.

I uninstalled and reinstalled Office Xp .

The OS is Win 98.

I think that the file related to SAMPLE formats is missing
or corrupted.

Please help.

Thanks in Advance.
Mann.
 
Isn't this due to the fact 08/3/2004 is actually entered as string? Does it
have a simple quote in front of it?
Normally when entering 08/3/2004 Excel will automatically recognize a date
and will format is as a date and it will appear as 08/03/2004... unless it
is entered as a string and then no matter what formatting you apply, it will
remain unchanged.

hth

Robin
 
Hi Mann,

Probably your cell is text. Format as date and re-enter.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Hi Guys,

Thanks for the prompt reply.
Well i checked the cells and applied the formulas too and
the value returned was Number.
So is there anything else we can look into.

Help appreciated.

Best Regards,
Mann.
 
Hi Mann!

How did you check it was a number?

Use:

=ISNUMBER(A1)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,

Thats precisely what i did and the value returned was TRUE.

Still the problem persists.

Please help.

Thanks,
Mann.
 
Hi Mann!

It looks like a workbook issue. But send any workbook with this
problem to the email address below and I'll take a look.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,

If you manage to find out what's going on, please let us know!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Hi Niek!

Mann sent a workbook and I found that it did have some text dates. I
changed using the add 0 approach and everything looks stable. There's
no code in the workbook.

Mann reckons it still reverts back to text and he's thinking in terms
of reformatting his system!!

I've suggested:

Start, Run,

"C:\Program Files\Microsoft Office\Office\Excel.exe" /Automation


Hoping to track a rogue Addin or something in the Personal.xls.

Very strange!!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman Harker said:
Hi Niek!

Mann sent a workbook and I found that it did have some text dates. I
changed using the add 0 approach
Click to expand...

I don't like this sort of workaround, which is like using '--' (two minus
signs) to tell a cell its contents are supposed to be a number. It isn't
done by "good" programmers, because the code isn't obvious to someone trying
to maintain the spreadsheet, and may not survive the next version.

Best ask Microsoft to cut down on the built-in intelligence that tries to
interpret what you're typing, and concentrate instead on improved means of
telling you how a cell is configured (see the 'empty cell' threads for
another example).

Having said that, dates can be made to work in XL once you have got the hang
of it.

Regards
 
Hi Chris!

I'm inclined to agree about cutting down on Excel's intellisence.

With Regional Settings at mm/dd/yy, Excel will interpret a user's
dd/mm/yy as a date if dd is <=12 and otherwise will interpret as text.
Users may be tempted to play with the text entries to get them as
dates but those accepted as dates are also wrong.

However, to restrict Excel's interpretation of entries to those that
are unequivocal dates (a very restricted number) would handicap the
larger numbers of users who are having no date input problems.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top