Prev: opening files error
Next: WorkSheet Cell Precedents?
From: Joe User on 7 Apr 2010 14:26
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.

From: Luke M on 7 Apr 2010 15:13
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(a)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.


From: Clif McIrvin on 7 Apr 2010 16:01
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(a)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.



From: Joe User on 7 Apr 2010 19:12
"Luke M" <lukemoraga(a)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(a)nospam.com> wrote in message
news:ejdidZo1KHA.260(a)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(a)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.
>
>

 | 
Pages: 1
Prev: opening files error
Next: WorkSheet Cell Precedents?