Solved

MS Excel - Custom Cell Format for Time (Duration)

Posted on 2014-09-18
3
4,220 Views
Last Modified: 2014-09-18
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
Comment
Question by:LGroup1
3 Comments
 
LVL 14

Accepted Solution

by:
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

by:Glenn Ray
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:
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
 

Author Closing Comment

by:LGroup1
ID: 40331364
Both great answers, thanks all !
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question