none
Formatting cells for time duration RRS feed

  • Question

  • I know the options to format time in Excel but I keep running into this problem:

    How do I represent time by duration? Examples:

    13:59

    15:35

    15:16

    7:32

    6:24

    31:08


    Sunday, March 11, 2018 7:51 PM

Answers

  • Hi,

        I am not clear on what you are trying to actually accomplish. Is the issue with the display or the way calculations are handled? They are two different issues.

        Internally, Excel stores date and time values as a decimal number where the integer portion is the number of days since Jan 0, 1900 (I know that Jan 0 doesn't exist, but Excel allows it) and the decimal portion is a fraction of 24 hours. I am almost certain it does all calculations with this value and really doesn't use the concepts of hours, minutes and seconds except for display.

    There are number of different functions (see Insert function Date & Time Category) which allow conversions back and forth between dates & times to get the underlying value, number of minutes, number of seconds etc.  I suggest that you convert all your times to their underlying values using TimeValue() to see what is happening.  This will help with separating the display issues from calculation issues.

    Hope this helps,

    Ethan


    Ethan Strauss


    • Edited by Ethan Strauss Wednesday, March 14, 2018 3:06 PM Typos
    • Marked as answer by ResidentX10 Tuesday, October 9, 2018 1:56 PM
    Wednesday, March 14, 2018 3:05 PM

All replies

  • I see in custom formatting the option for MM:SS
    Sunday, March 11, 2018 7:59 PM
  • I still have to key it in as 00:13:59

    Sunday, March 11, 2018 8:01 PM
  • But this still doesn't work right. What am I doing wrong here? I don't need the AM/PM and what's with this "12" in front of the entry?

    Sunday, March 11, 2018 8:04 PM
  • Here's my latest. I think I'm going to have to use H:MM:SS for calculations to work.

    Sunday, March 11, 2018 8:10 PM
  • This is what I got with h:mm:ss

    Sunday, March 11, 2018 8:14 PM
  • This got me closer using [h]:mm:ss but I wonder how this will work when I exceed 9/10 hours

    13:59

    15:35

    T: 28:94

    15:16

    07:32

    T: 51:42

    06:24

    31:08

    FT: 88.74/89 minutes



    Sunday, March 11, 2018 8:20 PM
  • The issue here is the seconds. 1/60 cannot be represented correctly in group calculations. You have to round the numbers either up or down but this will cause a loss of precision.

    Can anyone suggest a way to get better precision?


    Sunday, March 11, 2018 8:28 PM
  • Hi ResidentX10,
    I hope you will stop playing by yourself, and you will put your issue in order. i.e. wrap the issue up.

    Ashidacchi >> http://hokusosha.com/

    Monday, March 12, 2018 3:46 AM
  • Ashidacchi,

    Can you suggest a way to get better precision with time duration? If you can't then please don't  comment. It's just rude. Don't be a troll...

    Monday, March 12, 2018 9:23 AM
  • This comment is for you Ashidacchi.

    Excel because it does time duration wrong adds seconds to calculations. 

    If you didn't notice above the calculation I listed added 34 seconds which is wrong. 

    The exact calculation should of been 89:14. You don't see this as a problem?

    Imagine an organization of people doing calculations and then all merging them. This blur across an organization could be potentially catastrophic for financial or scientific endeavors. 


    • Edited by ResidentX10 Monday, March 12, 2018 12:47 PM
    Monday, March 12, 2018 12:47 PM
  • Hi,

        I am not clear on what you are trying to actually accomplish. Is the issue with the display or the way calculations are handled? They are two different issues.

        Internally, Excel stores date and time values as a decimal number where the integer portion is the number of days since Jan 0, 1900 (I know that Jan 0 doesn't exist, but Excel allows it) and the decimal portion is a fraction of 24 hours. I am almost certain it does all calculations with this value and really doesn't use the concepts of hours, minutes and seconds except for display.

    There are number of different functions (see Insert function Date & Time Category) which allow conversions back and forth between dates & times to get the underlying value, number of minutes, number of seconds etc.  I suggest that you convert all your times to their underlying values using TimeValue() to see what is happening.  This will help with separating the display issues from calculation issues.

    Hope this helps,

    Ethan


    Ethan Strauss


    • Edited by Ethan Strauss Wednesday, March 14, 2018 3:06 PM Typos
    • Marked as answer by ResidentX10 Tuesday, October 9, 2018 1:56 PM
    Wednesday, March 14, 2018 3:05 PM
  • I think my examples are pretty clear about what I want. I need the session times to add up correctly.

    I'm sorry you can't see that..

    Wednesday, March 14, 2018 4:56 PM
  • OK. Perhaps I should have understood from your examples.

    Did you look at anything else I said?


    Ethan Strauss

    Wednesday, March 14, 2018 7:13 PM
  • Ethan, 

    I'm extremely grateful for your patient comment. I will implement your suggestion. Thank you

    Also, I apologize for not replying. I stopped getting notifications on MSDN/Technet and was too busy to notice until over the weekend but I've marked your comment as the answer. Again, thanks for your comment. 

    Tuesday, October 9, 2018 1:59 PM
  • Does Power BI have this issue too?
    Tuesday, October 16, 2018 6:20 PM