Ask Your Question

How to add a column of Hours

asked 2017-09-07 19:24:05 +0100

simontfs gravatar image

updated 2017-09-07 19:30:46 +0100

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


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 flag offensive close merge delete


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

JohnSUN gravatar imageJohnSUN ( 2017-09-07 19:42:29 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-09-07 20:04:29 +0100

librebel gravatar image

Hello @simontfs,

you could just set the formula for cell F35 to:


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

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

edit flag offensive delete link more


I Have tried as suggested both ways




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

simontfs gravatar imagesimontfs ( 2017-09-07 22:41:23 +0100 )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.

librebel gravatar imagelibrebel ( 2017-09-08 01:17:46 +0100 )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

JohnSUN gravatar imageJohnSUN ( 2017-09-08 07:51:26 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-07 19:24:05 +0100

Seen: 81 times

Last updated: Sep 07 '17