Working with time DURATION not time of day

Question

I m doing some benchmarking  and I want to use Excel to produce graphs of the results  I ve got a simple but annoying problem which is baking my noodle   The problem is that Excel insists that  time  means a time of day  It refuses to let me work with time durations  When I try to say  three minutes and six seconds   it misinterprets this as  three minutes and six seconds past midnight   which isn t what I meant at all   I can work around the problem by laboriously converting everything to seconds  But then all my graphs are labelled in seconds  not minutes and seconds  Plus it s a pain to have to keep typing in  3 60 6 rather than just 3 06  And once I ve done it  I have to look at the formula to check whether I entered the data correctly  and didn t screw up the formula    Does anybody know how I can make Excel work with a time duration not anchored to a specific time of day

User · Answer

With custom format of a cell you can insert a type like this: d "days", h:mm:ss, which will give you a result like 16 days, 13:56:15 in an excel-cell.

If you would like to show the duration in hours you use the following type [h]:mm:ss, which will lead to something like 397:56:15. Control check: 16 =(397 hours -13 hours)/24

enter image description here

User · Answer

Highlight the cell s  column which you want as Duration  right click on the mouse to  Format Cells   Go to  Custom  and look for  h mm  if you want to input duration in hour and minutes format  If you want to include seconds as well  click on  h mm ss   You can even add up the total duration after that   Hope this helps

User · Answer

Let s say that you want to display the time elapsed between 5pm on Monday and 2 30pm the next day  Tuesday   In cell A1  for example   type in the date  In A2  the time   If you type in 5 pm  including the space  it will display as 5 00 PM  If you want the day of the week to display as well  then in C3  for example  enter the formula   A1  then highlight the cell  go into the formatting dropdown menu  select Custom  and type in dddd   Repeat all this in the row below   Finally  say you want to display that duration in cell D2  Enter the formula      a2 b2 - a1 b1   If you want this displayed as  22h 30m   select the cell  and in the formatting menu under Custom  type in h h  m m

User · Answer

What I wound up doing was   Put time duration in by hand  e g  1 min  03 sec   Simple but effective   It seems Excel overwrote everything else  even when I used the  custom format  given in some answers

User · Answer

You can easily do this with the normal  Time  data type - just change the format   Excels time date format is simply 1 0 equals 1 full day  starting on 1 1 1900   So 36 hours would be 1 5  If you change the format to  h  mm  you ll see 36 00   Therefore  if you want to work with durations  you can simply use subtraction  e g   A1  Start            36 00   1 5  A2  End              60 00   2 5   A3  Duration   A2-A1 24 00   1 0

User · Answer

The best way I found to resolve this issue was by using a combination of the above   All my cells were entered as a Custom Format to only show  HH MM  - if I entered in  4 06   being 4 minutes and 6 seconds  the field would show the numbers I entered correctly - but the data itself would represent HH MM in the background   Fortunately time is based on factors of 60  60 seconds   60 minutes   So 7H 15M   60   7M 15S - I hope you can see where this is going  Accordingly  if I take my 4 06 and divide by 60 when working with the data  eg  to total up my total time or average time across 100 cells I would use the normal SUM or AVERAGE formulas and then divide by 60 in the formula   Example   SUM A1 A5   60   If my data was across the 5 time tracking fields was the 4 06  3 15  9 12  2 54  7 38  representing MM SS for us  but the data in the background is actually HH MM  then when I work out the sum of those 5 fields are  what I want should be 27M 05S but what shows instead is 1D 03H 05M 00S  As mentioned above  1D 3H 5M divided by 60   27M 5S     which is the sum I am looking for     Further examples of this are    SUM G G   60 and    AVERAGE B2 B90  60  and  MIN C C   this is a direct check so no  60 needed here      Note that your  formula  or  calculation  fields  average  total time  etc  MUST have the custom format of MM SS once you have divided by 60 as Excel s default thinking is in HH MM  hence this issue    Your data fields where you are entering in your times should need to be changed from  General  or  Number  format to the custom format of HH MM   This process is still a little bit cumbersome to use - but it does mean that your data entry is still entered in very easy and is  correctly  displayed on screen as 4 06  which most people would view as minutes seconds when under a  Minutes  header    Generally there will only be a couple of fields needing to be used for formulas such as  best time    average time    total time  etc when tracking times and they will not usually be changed once the formula is entered so this will be a  one off  process - I use this for my call tracking sheet at work to track  average call    total call time for day

User · Answer

Use format d  days  h mm ss or  h  mm ss  depending on your needs   Say you have a duration of 30h 12m 54s    h mm ss -  6 12 54  not correct for a duration   h  mm ss -  30 12 54 d  days  h mm ss -  1 days 6 12 54   Variations are possible  I like something like d d  h h  mm m  ss s  which formats as 1d 6h 12m 54s

User · Answer

The custom format hh mm only shows the number of hours correctly up to 23 59  after that  you get the remainder  less full days   For example  48 hours would be displayed as 00 00  even though the underlaying value is correct    To correctly display duration in hours and seconds  below or beyond a full day   you should use the custom format  h  mm    In this case  48 hours would be displayed as 48 00   Cheers

User · Answer

I don t know how to make the chart label the axis in the format  X1 min   X2 sec   but here s another way to get durations  in the format of minutes with decimals representing seconds   5   30 sec   25   15 sec  etc    Suppose in column A you have your time data  for example in cell A1 you have 12 03 06  which your 3min 6sec data misinterpreted as 3 06 past midnight  and column B is free     In cell B1 enter this formula   MINUTE A1    SECOND A1  60 and hit enter return  Grab the lower right corner of cell B2 and drag as far down as the A column goes to apply the formula to all data in col A   Last step  be sure to highlight all of column B and set it to Number format  the application of the formula may have automatically set format to Time

[excel] Working with time DURATION, not time of day

Examples related to excel