Question

Locked

Having trouble with elapsed time cell format in Excel

By info ·
I'm trying to format cells in a worksheet to accept numbers as elapsed
time for a project requiring time tracking. For instance, 15:00 (fifteen
hours) is showing up as 3:00:00 PM in the formula bar. The standard
cell formatting options do not offer the format I need, however it is
available in the custom cell formatting options, but when I select it, it
does not apply to the cell. The Help says to use/type format code
[h]:mm to show elapsed time, but, how do I do that? More specifically,
where do I enter a format code? What am I not getting? (Perhaps I need
more coffee...)

Thanks in advance for any help that can be offered.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

In "Format Cells"

by LocoLobo In reply to Having trouble with elaps ...

Numbers tab

Select "Custom"

On the right side of the dialog box under the label "Type:" is a text box. You can enter your format there.

In mine (Excel 2003) there was already the custom format "[h]:mm:ss". It worked when selected. Then I went back and edited it to read "[h]:mm" and that worked too. If you already tried it and it's not working then I don't know.

Collapse -

Been there, done that...

by info In reply to In "Format Cells"

Thanks for your help, but, no, it's not working. I'm able to
select the custom format [h]:mm but it doesn't seem to be
applying to the cell. 1:30 of elapsed time shows as 1:30:00
AM in the formula bar.

I'm at a loss...

Any other ideas?

Collapse -

Hmm, your right

by LocoLobo In reply to Been there, done that...

So does mine. I had a formula in mine (=b7-b6) where b6 was the start time and b7 was the end time. what showed up in the cell was the correct hours:minutes. When I typed a time such as 5:30 in the cell the formula bar showed 5:30 AM. Wierd.

Collapse -

What about this?

by LocoLobo In reply to Having trouble with elaps ...

Would it work to input the duration in hours.decimal fractions (15.5 instead of 15:30)? Then you can use the formula

=MOD(B15,TRUNC(B15,0))*60

To convert the fractional component into minutes (where b15 contains your duration). From there you can build a string using the Concatenate function.

If you have to input the numbers in time format (15:30) then you could split it into numeric components using the LEFT and RIGHT functions. You will probably have to check for AM or PM. If PM add 12 hours.

Sorry that's a sketchy work around not a proper solution. I'll play with it when I get a chance.

Collapse -

It worked for me.

by jim In reply to Having trouble with elaps ...

I specified 24 hour format. The only problem I had was with negative elapsed times.

Back to Software Forum
6 total posts (Page 1 of 1)  

Software Forums