Counting hours

So I'm trying to setup a formula that will calculate the number of hours worked (and at what rate) based on the day a worker works and in what shift he does so.

The rules are:
Friday: Normal working hours are 7.5 hours within 6am-6pm. If he is still in this time frame and works over 7.5hrs, then over 7.5 and up to 10 hours is time-and-a-half (1.5) and over 10 hours is double-time (2). E.g. working from 6am to 6pm would mean 7.5hours of normal time, from 1.30pm to 4pm it's 2.5hrs of (1.5) and then 2hrs of (2).

Now still on Friday, if he works anytime between 6pm-12am, the pay is at 1.3.
From 12am to 4am it's 1.5 and from 4am till 6am it's 2.

Saturday: the first 4 hours are at 1.5 and anything after is at 2.

Mon-Thurs: normal time frame is 7.5hrs within 5am - 6pm. If we are in that timeframe still and >7,5hrs worked, then same rule applies from Friday. Also, 6pm-5am counts as 1.3

So essentially, I have two columns listing the start time and start finish, another column for the day, and then I would need for each entry to list how many hours were worked at: normal time, 1.3, 1.5 and 2.

I thought of using some massive IF function that would see what day it was, then compare the hours worked and try to calculate stuff, but maybe there is a better way? If anyone needs me to post the .xls for clarification, i can do that. Thnx

Hi Analyst

It is always a good habit to post a sample sheet. When you do so, you will get a lot of suggestion.

Thanks
Denis

Ok i have attached a sample.xls here:
http://www.upload2.net/page/download...ample.xls.html

Sorry about the #REF errors, but I am confused how to setup the formulae to do what I stated before. If anyone could help, I would appreciate that greatly.