How to add times in hour and minutes format

Solved/Closed
Ambucias Posts 47356 Registration date Monday February 1, 2010 Status Moderator Last seen September 1, 2021 - Jan 26, 2014 at 04:44 PM
Ambucias Posts 47356 Registration date Monday February 1, 2010 Status Moderator Last seen September 1, 2021 - Jan 27, 2014 at 05:38 PM
Hello

In the first column I have start time, in the second finish time and the third column the time elapsed ie A minus B = C

But if I add column C for total time I will get 9:90 rather than 10 hours 30 minutes

05:30 07:30 2:00
15:45 18:00 2:15
06:30 08:00 1:30
16:15 18:00 1:45
06:30 07:30 1:00
16:00 18:00 2:00

What must I do to correct this issue ?

Thank you for your help and precious time.

Best regards

6 replies

Ambucias Posts 47356 Registration date Monday February 1, 2010 Status Moderator Last seen September 1, 2021 11,233
Jan 26, 2014 at 06:04 PM
The total is still not correct

Try this and you will see:

05:30 07:30 2:00
15:45 18:00 2:15
06:30 08:00 1:30
16:15 18:00 1:45
06:30 07:30 1:00
16:00 18:00 2:00
04:30 06:15 1:45
16:00 18:00 2:00
06:00 07:30 1:30
16:00 18:00 2:00
06:15 07:30 1:15:00
16:15 18:00 1:45
4
Blocked Profile
Jan 26, 2014 at 05:22 PM
Hello Ambucias,

First of all can you make sure that columns A, B and C are set to "Time" so they display as HH:MM:SS.

Your formula for the C column should be B subtract A to get the difference

Then, the total of C needs to be =SUM(C<startcell>:C<endcell>)

this will give you the correct total.

if you wish to change the format of the cells from HH:MM:SS then you will need to select "Custom" and select HH:MM.
0
Ambucias Posts 47356 Registration date Monday February 1, 2010 Status Moderator Last seen September 1, 2021 11,233
Jan 26, 2014 at 05:39 PM
Thank you for rushing at my rescue

I had HH:MM as cell format, Note that column C has the correct answer it's the total of C that does not jive even though it's also HH:MM format.
0
Blocked Profile
Jan 26, 2014 at 05:51 PM
Yes, the cell which is the total of C needs to have a formula of =SUM(C1:C10) as an example
0
Ambucias Posts 47356 Registration date Monday February 1, 2010 Status Moderator Last seen September 1, 2021 11,233
Jan 26, 2014 at 05:59 PM
I know but it give me a total of 9hours 90 minutes rather than 10 hours 30 minutes.

Have you tested it?
0
Blocked Profile
Jan 26, 2014 at 06:01 PM
Yes, i've tested it. it sounds like you haven't set the format of the cell to time. its calculating all the time as numbers. click on the C cell so it highlights the whole column and change the format to custom and select HH:MM
0
Blocked Profile
Jan 26, 2014 at 06:06 PM
Right, I get a total time of 20:45
0
Blocked Profile
Jan 26, 2014 at 06:30 PM
Also, if the total time of C is going to exceed 24 hours then you need to format the cell this like [h]:mm
0
csdglobal Posts 3 Registration date Monday January 27, 2014 Status Member Last seen January 27, 2014
Jan 27, 2014 at 07:22 AM
try formatting your cells to TIME thne use the autosum function to add up the column. It should return the answer in hours, mins
0

Didn't find the answer you are looking for?

Ask a question
Ambucias Posts 47356 Registration date Monday February 1, 2010 Status Moderator Last seen September 1, 2021 11,233
Jan 27, 2014 at 07:38 AM
Thank you to both of you but I am still failing to get a correct sum:

05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:15 07:30 2:15
16:15 18:00 1:45
05:45 07:15 1:30
16:15 18:00 1:45
06:30 09:00 2:30
16:30 18:00 1:30
07:00 08:00 1:00
16:00 18:00 2:00
05:15 07:30 2:15
14:45 16:00 1:15
04:30 06:00 1:30
16:15 18:00 1:45
05:15 08:00 2:45
16:30 18:00 1:30
06:15 08:00 1:45
05:30 07:30 2:00
15:45 18:00 2:15
06:30 08:00 1:30
16:15 18:00 1:45
06:30 07:30 1:00
16:00 18:00 2:00
04:30 06:15 1:45
16:00 18:00 2:00
06:00 07:30 1:30
16:00 18:00 2:00
06:15 07:30 1:15
16:15 18:00 1:45

The total time in column C gives me 15:45 while there are more than 106 hours
0
Blocked Profile
Jan 27, 2014 at 09:11 AM
Sirs,

If you keep the "summed" column as a number, and multiple by 24(hours in a day), you get 111.75.




I used to have to think of every phone conversation as a CCS (COUNTED CALL SECOND), so my hour is actually 3600 seconds!

I hope this is what you were looking for. I have not added the totals up to be certain if it is actually getting the converstion correctly, test and verify before putting into production.

//ark
-Moderator/Contributor
0
Ambucias Posts 47356 Registration date Monday February 1, 2010 Status Moderator Last seen September 1, 2021 11,233
Jan 27, 2014 at 04:28 PM
If I keep the auto sum as a number I get: 4,66 X 24=111:75 in other words 111hours and 75 minutes, therefore, it should read 112 hours and 15 minutes. There are only 60 minutes in an hour.

Thanks but back to the drawing board.
0
Blocked Profile
Jan 27, 2014 at 04:36 PM
Ambucias!

because of the way Excel calculates hours (60 minute to 1 hour) you need to format the cell of the total time so that it displays hours over 24. You need to do a custom format and type in [h]:mm
0
Ambucias Posts 47356 Registration date Monday February 1, 2010 Status Moderator Last seen September 1, 2021 11,233
Jan 27, 2014 at 04:49 PM
@kieferschild,

Hurrah! Alleluia! I got 111:45 which is the correct answer.

Thank you, Thank you, Thank you!

And although you are not patriotic, God save the Queen and you as well!
0
Blocked Profile
Jan 27, 2014 at 04:50 PM
haha :) Glad we finally solved that one.
0
Blocked Profile
Jan 27, 2014 at 05:17 PM
@Ambucias...I wasn't certain if you needed to convert back into decimal. .75 = 3/4 = 45 minutes. So I wasn't certain if the total was counting the whole or not! YEA!
0