Disable auto (date) format?

  • Follow


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. 

0
Reply Joe 4/7/2010 6:26:42 PM

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 
news:%23Ib2f$n1KHA.776@TK2MSFTNGP04.phx.gbl...
> 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. 


0
Reply Luke 4/7/2010 7:13:09 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" <joeu2004> wrote in message 
news:%23Ib2f$n1KHA.776@TK2MSFTNGP04.phx.gbl...
> 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.



0
Reply Clif 4/7/2010 8:01:25 PM

"Luke M" <lukemoraga@nospam.com> 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" <lukemoraga@nospam.com> wrote in message 
news:ejdidZo1KHA.260@TK2MSFTNGP05.phx.gbl...
> 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 
> news:%23Ib2f$n1KHA.776@TK2MSFTNGP04.phx.gbl...
>> 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.
>
> 

0
Reply Joe 4/7/2010 11:12:46 PM

3 Replies
2057 Views

(page loaded in 0.044 seconds)


Reply: