Hours and minutes display as date in formula box

[Guest]

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.

 

[Ken Johnson]

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

 

[Guest]

try custon format of [h]mm

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[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 ass 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

 

[Ken Johnson]

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

 

[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 ass 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

 

[Ron Rosenfeld]

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

 

[Guest]

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.

 

[Ken Johnson]

Thanks Ron.
Ken Johnson

 

[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

 

[Ken Johnson]

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

 

[Guest]

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.