# How to add a column of Hours

On a sheet I am Calculating the total time from the F column as below;

F4=10:30
F5=09:30
F6=00:00
F7=08:15
F8=10:00
F9=11:00
F10=09:30
F11=01:00


I would like to add these hours Up. So in this Example, in F35 the result should be 59:45

If there is a way to do this, could someone please give a step by step guide on how I might do this?

Thank-you very much community.

edit retag close merge delete

May be =SUMPRODUCT(F4:F11/60)?

( 2017-09-07 19:42:29 +0200 )edit

Sort by » oldest newest most voted

Hello @simontfs,

you could just set the formula for cell F35 to:

=SUM(F4:F11)

then set the Number Format for cell F35 to [HH]:MM:SS

( or [UU]:MM:SS depending on your locale ).

more

I Have tried as suggested both ways

[HH]:MM:SS


and

[UU]:MM:SS


but I get a total of -444:15:00, any ideas?

( 2017-09-07 22:41:23 +0200 )edit
1. try setting the number format for cells F4:F11 to HH:MM:SS'.

for Scandinavian locale, try number format [TT]:MM:SS in cell F35, and TT:MM:SS for cells F4:F11.

( 2017-09-08 01:17:46 +0200 )edit

It seems to me that @simontfs will get a more understandable result when he puts the format DD.MM.YYYY HH:MM:SS` - in this case he can see something like as 28.12.1899 10:30:00, 30.11.1899 08:15:00. This will mean that although the cell displays 30:00, real current value is -1.5625 (or -10.5625, or any other). So next step - set format "0,0000" and search (and fix) negative values

( 2017-09-08 07:51:26 +0200 )edit