Solved

# MS Excel - Custom Cell Format for Time (Duration)

Posted on 2014-09-18
4,075 Views
MS Excel (all versions) have custom cell formats for time (for example h:mm:ss for hours/minutes/seconds) but apparently these formats are times relative to the time of the day.  For example, using the given format the time 0:5:17 would really equate to 12:05 (and seventeen seconds) AM and not five minutes and seventeen seconds.  When using that format the cell may indicate 0:5:17 but the function bar may actually show 12:05:17 am as it is assuming the time is a time of day.  How does one or should one create a custom cell format for a time setting that is not relative to the time of day but rather just time durations - for example. if one were recording times of those running a marathon or something and input 3:20:43 to indicate three hours, twenty minutes, and forty-three seconds rather than 3:20 am - and then wanted to do analysis on those times (as the analysis does not compute properly if MS Excel is assuming times of day) ?   If one uses a number field instead of times the math often does not work (for example, thirty seconds would compute to point three minutes rather than 50% of one minute). TIA
0
Question by:LGroup1

LVL 14

Accepted Solution

peetjh earned 250 total points
ID: 40331284
Just place a apostrophe before you enter the time. I just tested and I was able to perform a calculation of the difference between the times I tested with. '0:10:15
0

LVL 27

Assisted Solution

Glenn Ray earned 250 total points
ID: 40331328
Interestingly enough, you can do math on text strings that resemble time values and format the math results using
[h]:mm:ss
in order to display time values that are not time-of-day values.  For example:
The values in the "Start" and "End" columns are formatted as "Text".  The "Total" column is formatted using the custom format above and is just subtracting the Start value from the End value.  Note that even if minutes or seconds are entered with a single digit, the math is still correct.

-Glenn
0

Author Closing Comment

ID: 40331364
Both great answers, thanks all !
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…