Calculating total hours used daily?

Hi,

I have attached a template for a weekly rota. Everything is good in it except I do not know how to get total hours for mon-sun. I want the template to calculate the total hours used each day. Can someone help me please.

Hi,

In your daily total cell use this formula.

=SUMPRODUCT(IFERROR(IFERROR(C6:C25,0)-IFERROR(B6:B25,0),0))

This type of formula is an array and have to be entered by using CTRL+SHIFT+ENTER at the end, not by pressing ENTER.

Hope this is helpful.

Cheers

Total hours per day

=SUM(C6:C25)-SUM(B6:B25)

SUM ignores TEXT values/blanks

Hi
Try this

Formula:

=AGGREGATE(9,6,C6:C26)-AGGREGATE(9,6,B6:B26)

Hi,

Thanks for the replies, I cannot get any of the above formulas to work for me. Have you guys entered them in the template and if so, how did you do it? I have merged and centered the total cell for Monday and entered the above formulas and it has not worked for me.

Hi Voldo,

I think what we have all neglected to state is that you format of the cell has to be a custom format in order to the number of hours to show properly.

Change the cell format to "Custom" [hh]:mm

See attached example.

Cheers

Originally Posted by southward

Hi Voldo,

I think what we have all neglected to state is that you format of the cell has to be a custom format in order to the number of hours to show properly.

Change the cell format to "Custom" [hh]:mm

See attached example.

Cheers

Hi,

That's fantastic, its is working. Many thanks for all your help. Also thanks to everyone else for their suggestions.