Multiplying Time by A Number (not a cost)

Hi There

I need to calculate the total training hours spent by department.

I have 5 columns

Column A Column B Column C Column D Column E
Department Type of Training No of Delegate Session Duration Total Training
Row 1 Sales E-learning 10 02:20

Cells in Column C are formatted to number
Cells in Column D are formatted to HH:MM

The formula that I have used is:

=PRODUCT((HOUR(D8)+(MINUTE(D8)/60)),C8), however if the cells in Column E are also formatted to HH:MM this returns 00:00. Following a bit of googling (!) I changed he format in Column D to General.

This works fine if the training session was either :00, :15, :30, 00:45. However, where I have, for example a session lasting 02:20 for 7 people, I am returning a value of 16.3333333

On a Summary sheet I am them using SUMIFS to calculate total training hours by dept / by type of training. but my totals are coming out as decimal points, as if I change the format to HH:MM, the hours do not calculate correctly. (The total training hours need to be in HH:MM as it has to be manually input into a productivity system)

Any help or suggestions would be welcomed.

Thank you

Would you upload the workbook and manually add the results you expect clearly identifying which cells are results and which data.

Formatting is irrelevant for calculation purposes so don't get hung up on it. What matters is what's actually in the cells not how it's presented.