MS Excel - Custom Cell Format for Time (Duration)

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
LGroup1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JPIT DirectorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glenn RayExcel VBA DeveloperCommented:
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:
time values - textThe 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
LGroup1Author Commented:
Both great answers, thanks all !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.