how to sum up a calculated column of hours

Question

Level 1

14 points

how to sum up a calculated column of hours

I need to do what I thought was a simple task - calculating the sum of a column containing hours, calculated from neighbouring cells. In effect, a calculation of work hours.

The cells in the column are calculated as : finish time minus beginning time (C16−B16) i.e. 16:15-9:00 = 7h 15m

Time and date formatted as '"no date", time: 5:15'

I have attempted to calculate the sum of the column containing the calculated values (hours+minutes)' but so far with no luck.

I would love to hear if someone have a solution for this.

MacBook Pro with Retina display, iOS 10.3.2

Posted on Sep 14, 2017 4:09 PM

Reply

Answer 1

Best reply

Walentin Author

Level 1

14 points

Sep 14, 2017 10:18 PM in response to Wayne Contello

Thanks for the response Wayne.

However, the =sum(D) I cannot use, since there are also other values in the column.

I have found though what was really the problem.

With values in many of the cells not yet being filled in, the calculated value for those cells were 0 (Zero), therefor the error:

"Values being summed by SUM must be either all durations or all numbers, unless one value is a date."

I was certain, - as you also state, that one time minus another time can be summed, and so it can, but not if some sells contain zero values.

I have since been solved this, using : IF(ISBLANK(C16),"",C16−B16

And the durations in the column can now be summed as the column no longer contains zero values.

Reply

Answer 2

Walentin Author

Level 1

14 points

Sep 15, 2017 7:35 AM in response to Walentin

One problem I do have left however, is to convert the sum of the calculated (x hrs x min) durations to a number / currency format, so that cell D33 (27Hr 45min) times 15, returnes $ 411.75, (15*.45)+(27*15) rather than 405h 15m

Reply

Answer 3

Barry

Level 9

59,867 points

Sep 15, 2017 3:25 PM in response to Walentin

"I also see that you noticed my calculation mistake"

Actually, I hadn't. I registered that you were getting a duration result and wanted a numeric result, set up the necessary formula, used the duration value from your example, and assumed Numbers did the math correctly. didn't look back to see if it was the 'expected' result. 😉

Regards,

Barry

Reply

Answer 4

kyoung

Level 1

8 points

Sep 16, 2017 9:26 AM in response to Walentin

This response and formula seems to have explained the workaround for the problem but I wonder if the parenthesis are correct. There is one closed set and one open set in the formula. Is this actually how it is supposed to be?

Reply

Answer 5

SGIII

Level 8

35,348 points

Sep 16, 2017 11:49 AM in response to kyoung

kyoung wrote:

I wonder if the parenthesis are correct. There is one closed set and one open set in the formula. Is this actually how it is supposed to be?

No. The formula

=IF(ISBLANK(C16),"",C16−B16

will not work.

It is missing a parenthesis at the end.

SG

Reply