Calculate hours that fall between a specific period for multiple shifts across a week

Hello,

I have a range of shift start / finish times (including DOW) for which I need to calcuate how many hours fall between 20:00 Saturdays and 06:00 Mondays. I have included the attachment showing an example of the data set. Range M7:M18 is what I am trying to fill, with the shift start times in K7:K18, shift end time in L7:L18, the start of the time period I want hours for in J2, the finish time in J3.

The base data for the shift start / finish times is in the format of e.g. 1/01/1900 4:20:00 a.m. for an 04:20 start time on a Saturday.

This has stumped me, so any suggestions / ideas would be gratefully received - I have many thousands of shifts to calulate so would rather not do it manually!

Many thanks

Thanks for the quick reply - that will give me the total shift length, but I am after only the hours within the shift that fall between 8PM Saturdays (referenced in J2) and 6AM Mondays (in J3) - so for the first shift on the example it would be the full 10hrs 20", but for the last one on the example I it would only be 2hrs (as opposed to the total shift length of 10hrs 15")

Thanks!

Hmm, I don't have actual date / times, as this is for a proposed roster as opposed to the actual shifts people have worked - this is an output from our roster system, I am trying to assess the impact of penal rates for a variety of potential times. Is there a function I should use to change the base start / finish time to a different format then to make it easier? Thanks

I have close to 10,000 shifts / lines to work this out for, and will need to vary the times I need to check (i.e. to see the difference between if it was 06:00 Saturdays vs 10:00 Saturdays vs 22:00 Fridays etc), so might be a little tricky to manually change all of them, and then somehow change back...ideally after some sort of function, if not possible I could try and write up a macro to do something along the lines of your suggestion though, thanks

Thanks for these suggestions - I ended up meshing them together a bit and creating an IF Statement - I first took the base shifts and calculated a proper date/time stamp based on a ficticious date (just by adding a whole date to the base time), then calculated the hours that fell in between:

=(IF(OR(G12<$F$6,F12>$G$6),0,IF(G12>$G$6,$G$6,G12)))-(IF(OR(F12>$G$6,G12<$F$6),0,IF(F12>$F$6,F12,$F$6)))

Where G12 is the date/time of shift end, F12 is the date/time of shift start, F6 is the date/time I want to calculate the hours from and G6 is the date/time to calculate too. Then all I need to do is vary F6 and/or G6 to see the impact of changing the start / finish times for penal rates to kick in.

I suspect there may be an easier way to do it, but it works so happy with that - thanks again for the help

Here is a section of the completed file
Cheers