Calculating Overtime between intervals

Hi all,

I am trying to calculate overtime hours in a interval, i will try to explain:

Day1: 8hr
Day2: 9hr
Day3: 9hr
Day4: 12hr
Day5:9,5hr
Day6: 8hr
Day7: 11hr

I need to know how many hours i have worked between 8 and 10 hours a day, and know how many hours i have worked above 10h/day.

According to my worked hours i know that i have worked 2 days with 8 hours using =count, i know that i have worked 3,5 hours above 8hr/day and below 10hr/day and 3 hours above 10hr/day.

My point is to know how many hours and not how many cells between 8 and 10 and how many cells above 10.

How can i do this?


Best regards,
Rodrigo

Attached is an example. Hours data is in column B:

For hours 8-10

=(SUMIF(B2:B8,">8")-SUMIF(B2:B8,">10"))-(COUNTIF(B2:B8,">8")-COUNTIF(B2:B8,">10"))*8

For hours over 10

=(SUMIF(B2:B8,">10"))-(COUNTIF(B2:B8,">10"))*10

Hi!

The formula for hours over 10 is working perfectly! thanks

for hours between 8 and 10 is not working well, as you may verify: Day 2 has 1hr, day 3 has 1hr, day 4 2hr, day 5 1,5hr and 7 2hr. So this is 7,5hrs between 8 and 10.

Do you have ideas?

Thanks
Rodrigo

Originally Posted by 6StringJazzer

Attached is an example. Hours data is in column B:

For hours 8-10

=(SUMIF(B2:B8,">8")-SUMIF(B2:B8,">10"))-(COUNTIF(B2:B8,">8")-COUNTIF(B2:B8,">10"))*8

For hours over 10

=(SUMIF(B2:B8,">10"))-(COUNTIF(B2:B8,">10"))*10