Questions

Having trouble with elapsed time cell format in Excel

Tags:
+
0 Votes
Locked

Having trouble with elapsed time cell format in Excel

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.
  • +
    0 Votes
    LocoLobo

    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.

    +
    0 Votes
    info

    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?

    +
    0 Votes
    LocoLobo

    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.

    +
    0 Votes
    LocoLobo

    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.

    +
    0 Votes
    jim

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

  • +
    0 Votes
    LocoLobo

    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.

    +
    0 Votes
    info

    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?

    +
    0 Votes
    LocoLobo

    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.

    +
    0 Votes
    LocoLobo

    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.

    +
    0 Votes
    jim

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