SUM formula for adding time durations

  • Thread starter Thread starter Batfink
  • Start date Start date
B

Batfink

Struggling to get a sensible result when adding a column
of mm:ss time durations.
Do I need a particular format for the column ?
 
Batfink,

If you've entered times values as 11:07, Excel thinks
that it is eleven hours and seven minutes. If you want it
to be minutes and seconds instead, your SUM formula
must divide by 60.

=SUM(A1:A10)/60

Also, you may need to change the number format of
the SUM cell, and perhaps also the list of time entries
to display hours above 24:

Format - Cells - Number tab - Custom category
Type: [h]:mm
or
Type: [h]:mm:ss
 
Thanks Ture,

Every time I try and change the format though, it
transposes what is minutes and seconds to hours, minutes
and seconds. so 09:53 should be 9 minutes 53 seconds, but
gets transposed to 9 hours 53 minutes and no seconds.

Any ideas ?

-----Original Message-----
Batfink,

If you've entered times values as 11:07, Excel thinks
that it is eleven hours and seven minutes. If you want it
to be minutes and seconds instead, your SUM formula
must divide by 60.

=SUM(A1:A10)/60

Also, you may need to change the number format of
the SUM cell, and perhaps also the list of time entries
to display hours above 24:

Format - Cells - Number tab - Custom category
Type: [h]:mm
or
Type: [h]:mm:ss

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden

Struggling to get a sensible result when adding a column
of mm:ss time durations.
Do I need a particular format for the column ?
Click to expand...


.
Click to expand...
 
As Ture wrote, XL parsed 09:53 as 9 hours 53 minutes. A cell's
number format has *nothing* to do with how the parser interprets the
input. If you want to enter 9 minutes 53 seconds, enter it as 0:9:53.

OTOH, as long as your times are <23 minutes 59 seconds, it really
doesn't matter whether you enter them as minutes:seconds or
hours:minutes - they can be formatted to display the same. But any
math should take into account your using hours:minutes, as Ture's
SUM(A1:A10)/60 suggestion does.
 
I have done something similar to keep track of time worked. Since this was what I used it for, there may be more information her than you need. First of all, the cells where time would be entered were formatted as time-1:30 PM, but the cells where these times were being added and subtracted were formatted as time-13:30 (to be on 24 hours). I'm not sure if that was correct or not, but it worked.

This is the formula I used to add the time NOT worked together (cell K4):

=TEXT(F4-E4,"H:MM")+TEXT(D4-C4,"H:MM")+TEXT(H4-G4,"H:MM"

Then I had this formula to figure out total time worked (cell B4 was start time/cell I4 was the ending time

=TEXT(I4-B4,"H:MM")-K

Lastly I used this formula to figure out how many hours were still needed to make a full day (cell L4 (total time worked)was subtracted from 8 hours, then any "leave" taken was subracted as well)

=VALUE("8:00:00")-((TEXT(L4,"h:mm")-(TEXT(N4,"h:mm"))

Keep in mind when entering time, you need to make sure that you enter it on the 24 hour clock to ensure you get AM & PM correct

HTH -

Kell
 
Back
Top