Working hours attempt URGENT

In a bit of a pickle, I thought id solve this by today and am running myself in circles...

I have been working on a spreadsheet that requires me to calculate the number of working hours (9-5) spent on a number of tasks that can either be resolved in a day or span multiple days/weeks/months. Oddly, my attempt is correct in some instances (marked green) but incorrect in other (marked red).

I would be eternally grateful if someone could dig me out of a big hole and provide the correct formula.

Many thanks

Max

Thanks for highlighting the date, that was an error.

I have however corrected and it still yields the wrong hour count.

I have attached an updated spreadsheet to refer to, which also quotes what the hours should be.

Help appreciated.

Max

Hours between 11/28/2013 11:00 & 11/27/2013 11:21 is 9:21 OR 8:21?

Originally Posted by Pepe Le Mokko

Just custom format J2 and O2 as [hh]:mm to get the correct values. (hh:mm won't do when over 24 hours)

It seems that the values you indicated as "should be" are not correct.

How do I custom format as per above?

You are correct 9:21 should have read 8:21

Many thanks all

Quick supplementary question,

Can someone explain to me what the MOD function does in reference to the formula in M2 of the spreadsheet in the previously attached workbook?

Many thanks

Max

Many thanks