Answered by:
Formatting cells for time duration
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
 Edited by ResidentX10 Sunday, March 11, 2018 8:09 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
All replies


I still have to key it in as 00:13:59
 Edited by ResidentX10 Sunday, March 11, 2018 8:03 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
 Edited by ResidentX10 Sunday, March 11, 2018 8:26 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?
 Edited by ResidentX10 Sunday, March 11, 2018 8:36 PM



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

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



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.
