How to add times in hour and minutes format [Solved/Closed]

Report
Posts
47523
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 22, 2020
-
Posts
47523
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 22, 2020
-
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

Posts
47523
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 22, 2020
9,043
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month


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.
Posts
47523
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 22, 2020
9,043
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.
Blocked Profile
Yes, the cell which is the total of C needs to have a formula of =SUM(C1:C10) as an example
Posts
47523
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 22, 2020
9,043
I know but it give me a total of 9hours 90 minutes rather than 10 hours 30 minutes.

Have you tested it?
Blocked Profile
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

Right, I get a total time of 20:45
Blocked Profile
Also, if the total time of C is going to exceed 24 hours then you need to format the cell this like [h]:mm
Posts
3
Registration date
Monday January 27, 2014
Status
Member
Last seen
January 27, 2014

try formatting your cells to TIME thne use the autosum function to add up the column. It should return the answer in hours, mins
Posts
47523
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 22, 2020
9,043
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

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
Blocked Profile
haha :) Glad we finally solved that one.
Blocked Profile
@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!
Posts
47523
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 22, 2020
9,043
I understand but I omitted to mention that the total time had to be multiplied by an hourly rate for the use of a machine.
Blocked Profile
OH, scope creep then, huh. Failed to mention... tisk tisk tisk! :)
Posts
47523
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
October 22, 2020
9,043
I just got a slap and deserved it.