Weekend Shift Premiums

[carguy_19]

Hey,

I am currently creating a shift premium form (we are a 24/7 facility) for my place of work. Due to working shift work, we are entitled to shift premiums based on the hours we work as seen below:

Evening premiums 16:00hrs - 24:00hrs
Night Premiums 24:00hrs - 08:00hrs
Week-end Premiums - Fri 24:00 - Sun24:00hrs

I have managed to figure out the formula for the evening and Night premiums but I am at a loss for the week-end premiums. I have the excel sheet formatted as seen below:

Date(I15) . Start Time(J15) . End Time(K15) . Total premiums(L15)

Basically, i would like to be able to enter the date and my start/end time of the shift and it calulate how many weekend premiums i am entitled to.
Example 1: If I work a Friday night shift from 19:00hrs - 07:00hrs i would get 7 weekend premiums as the last 7 hours are past fri 24:00hrs. (I would like this to show in L15)
Example 2: Saturday day shift from 07:00 - 19:00hrs is a full 12 hours of weekend premiums because it falls between fri 24:00 and Sun 24:00hrs.
Example 3: Sunday night shift 19:00 - 07:00hrs would only qualify for 5 hours because the last 7 hours of the shift falls on Monday.

I look foward to any help provided.

Thanks

[NeedForExcel]

Please attach a sample workbook

[carguy_19]

Here is a copy of what i have completed thus far.

It is still a work in progress.

Thanks for your quick reply.

[carguy_19]

Still no luck with figuring this out... Any help would be appreciated!

Cheers...