Disable auto (date) format?

  • Thread starter Thread starter Joe User
  • Start date Start date
J

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.
 
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.##"))
 
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
 
Luke M said:
Your logic sounds circular. By using General setting,
you're saying that you want XL to use whatever it thinks
is the natural format.
Click to expand...

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


----- original message ------
 
Back
Top