Hours and minutes display as date in formula box

When I enter hours and minutes in a cell (example: 93:11:00) the formula box
shows 1/3/1900 9:11:00pm. How do you get the formula box to display 93:11:00?
Also, is there a way to display only hours and minutes (93:11) since I don't
work with seconds? Thanks.

try custon format of [h]mm
--
paul
remove nospam for email addy!



"Dave" wrote:

> When I enter hours and minutes in a cell (example: 93:11:00) the formula box
> shows 1/3/1900 9:11:00pm. How do you get the formula box to display 93:11:00?
> Also, is there a way to display only hours and minutes (93:11) since I don't
> work with seconds? Thanks.

On Thu, 5 Jan 2006 17:36:02 -0800, "Dave" <Dave@discussions.microsoft.com>
wrote:

>When I enter hours and minutes in a cell (example: 93:11:00) the formula box
>shows 1/3/1900 9:11:00pm. How do you get the formula box to display 93:11:00?

As far as I know, there is no way to control what is displayed in the formula
bar.


>Also, is there a way to display only hours and minutes (93:11) since I don't
>work with seconds? Thanks.

Select the cell; then Format/Cells/Number/Custom Type: [h]:mm




--ron

Hi Dave,
One way is to apply the custom format h:mm to the cell(s) and type an
apostrophe (') before the value eg '93:11. The apostrophe appears in
the formula bar but not in the cell, and you will be able to do
calculations this way too.
To apply the format go Format>Cells>Number>Custom> then type h:mm in
the Type: box.
Ken Johnson

On 5 Jan 2006 18:03:08 -0800, "Ken Johnson" <KenCJohnson@gmail.com> wrote:

>Hi Dave,
>One way is to apply the custom format h:mm to the cell(s) and type an
>apostrophe (') before the value eg '93:11. The apostrophe appears in
>the formula bar but not in the cell, and you will be able to do
>calculations this way too.
>To apply the format go Format>Cells>Number>Custom> then type h:mm in
>the Type: box.
>Ken Johnson

That is misleading.

Although operators will usually interpret an entry of the form '93:11
correctly, commonly used worksheet functions will not do so.

Try using the SUM function on that cell -- it evaluates to zero.


--ron

Hi Ron,
I meant to ask...
What is the difference between [h]:mm and h:mm?
Ken Johnson

On 6 Jan 2006 04:26:30 -0800, "Ken Johnson" <KenCJohnson@gmail.com> wrote:

>Hi Ron,
>I meant to ask...
>What is the difference between [h]:mm and h:mm?
>Ken Johnson

Without the brackets around the h, the h will show h mod 24, so 93:11 would
show as 21:11


--ron

"Ron Rosenfeld" wrote:

> On 6 Jan 2006 04:26:30 -0800, "Ken Johnson" <KenCJohnson@gmail.com> wrote:
>
> >Hi Ron,
> >I meant to ask...
> >What is the difference between [h]:mm and h:mm?
> >Ken Johnson
>
> Without the brackets around the h, the h will show h mod 24, so 93:11 would
> show as 21:11
>
>
> --ron
> It works. Thanks to all of you.

Hi Ron,

>Try using the SUM function on that cell -- it evaluates to zero.

Thanks for pointing out my mistake.
Everything seemed fine when I tried A2 - A1 so I unwittingly assumed
all calculations would be fine. I now see that worksheet functions will
only work with these cells, with the ' prefix, if they are entered as
their value eg SUM(VALUE(A1),VALUE(A2)).
Ken Johnson

Hi Ron,
Thanks for that.
I just tried A2 - A1, which worked, so I assumed everything OK with
calculation.
A classic case of "when I assume I put an *** before u and me"!
I'll try to be more careful in future, but with so much to learn about
Excel, I can't make any promises.
Ken Johnson

Hi Ron,
This might appear twice, something seemed to go wrong first time.
Anyhow, thanks Ron, I just tried A2-A1 and it worked so I assumed all
calculations would be fine. Looks like a classic case of "When one
assumes one puts an *** before u and me".
I'll try to be more careful in future, but with so much to learn about
Excel I can't make any promises.
Ken Johnson

Thanks Ron.
Ken Johnson

Hi Ron,
I notice that worksheet functions only work if the cells with the '
prefix are entered inside the VALUE function.
Thanks for pointing that out.
Ken Johnson

You want the Time Category in Format Cells:Number. You can select the format
37:30:55, which will display 93:11:00, then choose Custom and delete the
seconds. The resulting customized category will look like [h]:mm and will
show hours and minutes but not seconds.

"Dave" wrote:

> When I enter hours and minutes in a cell (example: 93:11:00) the formula box
> shows 1/3/1900 9:11:00pm. How do you get the formula box to display 93:11:00?
> Also, is there a way to display only hours and minutes (93:11) since I don't
> work with seconds? Thanks.