Excel - Disable auto (date) format?

Asked By Joe User on 07-Apr-10 02:26 PM
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 do not mind if it turns off all "intelligent" autoformat selection.

I am using MS Office Excel 2003 SP3.




Luke M replied to Joe User on 07-Apr-10 03:13 PM
Your logic sounds circular. By using General setting, you are saying that you
want XL to use whatever it thinks is the natural format. But, then you say
that you would  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
Clif McIrvin replied to Joe User on 07-Apr-10 04:01 PM
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 replied to Luke M on 07-Apr-10 07:12 PM
Yes, that makes sense.  I had not thought of it that way.  Thanks.


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