Ask Your Question

How to sum a column of hours:minutes values

asked 2017-11-01 08:39:49 +0100

jennyg gravatar image

I want to total a column of Hours:minutes values.

If I use a1+A2+A3 etc, it works if I format the cell in which I want the total to appear as [HH]:MM:SS

But if I use the SUM function for a range of cell (much more convenient) I get the answer 00:00:00.

Is there some other function I should be using or is this a bug?

This is to total a series of glider flight durations express as hours and minutes, to get a total number of hours.

edit retag flag offensive close merge delete


Possibly the source cells are actually not dates, but text formatted as dates. The plus converts them implicitly, but SUM cannot.

Mike Kaganski gravatar imageMike Kaganski ( 2017-11-01 08:42:20 +0100 )edit

I tried formatting the source cells as [hh]:mm as well but same result.

jennyg gravatar imagejennyg ( 2017-11-01 08:45:12 +0100 )edit

and does the source cell format change when you do that?

Please post a sample document to some share and post a link here to check

Mike Kaganski gravatar imageMike Kaganski ( 2017-11-01 08:53:06 +0100 )edit

I don't think it did, but how can you tell?

jennyg gravatar imagejennyg ( 2017-11-01 09:09:15 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-11-01 09:03:09 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I think I have solved the problem myself after reading Mike's comment.

The list of times was imported from a csv file - ticking the option "detect special numbers" at the import stage seems to make the SUM function handle them correctly, as long as the total cell is formatted [HH]:MM:SS

Thanks Mike Kaganski

edit flag offensive delete link more
Login/Signup to Answer

Question Tools



Asked: 2017-11-01 08:39:49 +0100

Seen: 4,599 times

Last updated: Nov 01 '17