Disable auto (date) format?

[Joe User]

In A3, I have a formula of the form =A1/A2/24, where A2 is time (date serial
number) formatted h:mm:ss. (A1 is a number formatted as General.)

When A3 is formatted as General, Excel changes the format to h:mm:ss every
time I edit A3. I have to change the format back to General manually, which
is a nuisance.

I can avoid this by formatting A3 as Number. But I would like it remain
General.

Is there any option setting that disables this autoformat heuristic?

I don't mind if it turns off all "intelligent" autoformat selection.

I am using MS Office Excel 2003 SP3.

 

[Luke M]

Your logic sounds circular. By using General setting, you're saying that you
want XL to use whatever it thinks is the natural format. But, then you say
that you'd like a number to display (indiciating that you *do* care what the
format is). But then you go back and say you want a General format??

If you *insist* on having the cell format remain General, you could
accomplish this by losing precision with this formula (or something similar)
and still have the cell format be General.
=VALUE(TEXT(A1/A2/24,"0.##"))

 

[Clif McIrvin]

Sounds like what you want is to force Excel to ignore the date/time type
in A2 and treat that value as the date serial number (that is, just a
floating point number) instead.

Try using =A1/N(A2)/24 and see if that does what you want.

Look up the help on the N worksheet function.

Clif

 

[Joe User]

Your logic sounds circular. By using General setting,
you're saying that you want XL to use whatever it thinks
is the natural format.

Yes, that makes sense. I had not thought of it that way. Thanks.


----- original message ------