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.

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.##"))

--
Best Regards,

Luke M
"Joe User" <joeu2004 wrote in message
...
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.

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" <joeu2004 wrote in message
...
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" wrote:
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 ------

"Luke M" wrote in message
...
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.##"))

--
Best Regards,

Luke M
"Joe User" <joeu2004 wrote in message
...
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.