Solved

MS Excel - Custom Cell Format for Time (Duration)

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 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

17 Experts available now in Live!

Get 1:1 Help Now