Solved

MS Excel - Custom Cell Format for Time (Duration)

Posted on 2014-09-18
3
3,933 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now