Calculating shift types and hours applicable for those times

Hi All,

Just joined - Thanks for viewing my post!

I have a very large spreadsheet where I need a formula to calculate how many hours relate to different portions of a shift worked.

The splits of the shifts are comprised as follows:

Day shift: 06:00 to 20:00

Night shift: 20:00 to 06:00

Saturday: All day until midnight

Sunday: All day until midnight

UK Public Holiday: All day until midnight

The break should be taken from the larger time period of the split within the shift.

I need to show how the shift is comprised, how many hours and minutes relate to each slice of the shift.

Any and all help is greatly appreciated, attached is an example of how the data is arranged.

Thanks,

Doug

Shifts are tricky, particularly when they overlap into the ensuing day. Excel doesn't know the difference between 4 AM this morning when I started my shift and 4 AM "tonight" when I will end my shift. Take a look at the attachment and see if it catches the necessary contingencies. It treats 2 AM Saturday morning as a continuation of Friday's shift, because that seemed sensible since it'd still be attached to Friday's date according to your setup. The attached version does NOT deduct the break, it merely calculates the hours that fall into each shift. I'd advise deducting the break when you do your second round of calculations to get the pay totals, it'll fit in more easily there. It also doesn't calculate the holidays, because I don't know that those are stored anywhere within Excel. You could easily create a hidden sheet that lists holidays and then use a variation of the Sat/Sun formula to cover those. Take a look, see if it works: