# How to add times in hour and minutes format

Solved/Closed
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 - Jan 26, 2014 at 04:44 PM
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 - 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
Related:

## 6 responses

Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,165
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
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.
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,165
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.
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
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,165
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?
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
Blocked Profile
Jan 26, 2014 at 06:06 PM
Right, I get a total time of 20:45
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
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

Didn't find the answer you are looking for?

Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,165
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
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
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,165
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.
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
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,165
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!
Blocked Profile
Jan 27, 2014 at 04:50 PM
haha :) Glad we finally solved that one.
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!