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.

Hours and minutes display as date in formula box
 

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 FormatCellsNumberCustom then type h:mm in
the Type: box.
Ken Johnson

Hours and minutes display as date in formula box
 

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.

Hours and minutes display as date in formula box
 

On Thu, 5 Jan 2006 17:36:02 -0800, "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?

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

Hours and minutes display as date in formula box
 

On 5 Jan 2006 18:03:08 -0800, "Ken Johnson" 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 FormatCellsNumberCustom 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

Hours and minutes display as date in formula box
 

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

Hours and minutes display as date in formula box
 

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

Hours and minutes display as date in formula box
 

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

Hours and minutes display as date in formula box
 

On 6 Jan 2006 04:26:30 -0800, "Ken Johnson" 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

Hours and minutes display as date in formula box
 

"Ron Rosenfeld" wrote:

On 6 Jan 2006 04:26:30 -0800, "Ken Johnson" 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.

Hours and minutes display as date in formula box
 

Thanks Ron.
Ken Johnson

Hours and minutes display as date in formula box
 

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

Hours and minutes display as date in formula box
 

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

Hours and minutes display as date in formula box
 

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.