Calculate number of hours within range

[miego]

Hello, I'm trying to figure out the simplest and most effective way to calculate if working hours are within specific range i.e.
Employe starts his work
21:00 ends at 7:00
and I'd like to count how many hours are within 20-24; 24-4; 4-6; because different pay ratio.
I tried with nested if, but succed only with 24-4 range.

Please Login or Register  to view this content.

PS. now I know I've used hour minute functions, when it's the same if you multiply it by 24

[daddylonglegs]

Hello miego

For any period that doesn't start at midnight, end at midnight or cross midnight, like 4-6 you can use this formula

=(B1<A1)*(6-4)+MEDIAN(B1*24,6,4)-MEDIAN(A1*24,6,4)

for a period ending at midnight like 20-24 you can use this version:

=(B1<A1)*4+MAX(B1*24,20)-MAX(A1*24,20)

where 20 is the start time of the period and 4 is the length of it

....and for periods starting at midnight like 24-4 you can use this version

=(B1<A1)*4+MIN(B1*24,4)-MIN(A1*24,4)

where the 4s represent the end time of the period

These formulas work for any shift length up to 24 hours, format result cells as number or general

[miego]

Thank you a lot. I also adopted this code to subtract unpaid break within this range.
I would never thought it could be done with such simple functions.