overtime calculation with different rate of pay

Could you tell us about the current data set up you have. What are all of your data points that are already entered and then we can probably give you a great method for achieving this. From the sounds if it you could use a few small tables to achieve this easily, but to know for sure if that would work we need to know everything you currently have.

Thank you for the quick reply.
Actually this is a sheet for agency. and there are different rates for them
i want to attach the file so you guys can see it but i dont know how to. I will try to copy and paste the sheet data so you can have a flavour.
The first one was done by my manager trying to get it done on one sheet

Names Nigel Porter
Date
30/1/11
Sunday
Time
Normal
6:00:00 AM
7:00:00 AM
8:00:00 AM
9:00:00 AM
10:00:00 AM
11:00:00 AM
12:00:00 PM
1:00:00 PM
2:00:00 PM
3:00:00 PM
4:00:00 PM
5:00:00 PM
6:00:00 PM
7:00:00 PM d
8:00:00 PM d
9:00:00 PM d
10:00:00 PM d
11:00:00 PM d
12:00:00 AM d
1:00:00 AM d
2:00:00 AM d
3:00:00 AM
4:00:00 AM
5:00:00 AM
16
Total Hours 7.5
Total Hours 10.5
Weekend Day hours 8
Weekend night hours 0
Date
31/01/2011
Monday
Time
6:00:00 AM
7:00:00 AM
8:00:00 AM
9:00:00 AM
10:00:00 AM
11:00:00 AM
12:00:00 PM
1:00:00 PM
2:00:00 PM
3:00:00 PM
4:00:00 PM
5:00:00 PM
6:00:00 PM
7:00:00 PM
8:00:00 PM
9:00:00 PM
10:00:00 PM
11:00:00 PM
12:00:00 AM
1:00:00 AM
2:00:00 AM
3:00:00 AM
4:00:00 AM
5:00:00 AM
Total Hours -0.5
Week Day hours -0.5
Week night hours 0
Date
01/02/2011
Tuesday
Time


then i tried to do in other way but now im stuck. it woul dbe better if i could attach the file but i cant.




Names

Date 6.2.11
Sun Hours Day H Night H
6:00:00 AM 0 0 0
7:00:00 AM 0
8:00:00 AM 0
9:00:00 AM 0
10:00:00 AM 0
11:00:00 AM 0
12:00:00 PM 0
1:00:00 PM 0
2:00:00 PM 0
3:00:00 PM 0
4:00:00 PM 0
5:00:00 PM 0
6:00:00 PM 0
7:00:00 PM 0
8:00:00 PM 0
9:00:00 PM 0
10:00:00 PM 0
11:00:00 PM 0
12:00:00 AM 0
1:00:00 AM 0
2:00:00 AM 0
3:00:00 AM 0
4:00:00 AM 0
5:00:00 AM 0

Date 7.2.11 0
Mon Hours Day H Night H
6:00:00 AM 0 11.5 0
7:00:00 AM 1 1
8:00:00 AM 1 2
9:00:00 AM 1 3
10:00:00 AM 1 4
11:00:00 AM 0.5 4.5
12:00:00 PM 1 5.5
1:00:00 PM 1 6.5
2:00:00 PM 1 7.5
3:00:00 PM 1 8.5
4:00:00 PM 1 9.5
5:00:00 PM 1 10.5
6:00:00 PM 1 11.5
7:00:00 PM 0
8:00:00 PM 0
9:00:00 PM 0
10:00:00 PM 0
11:00:00 PM 0
12:00:00 AM 0
1:00:00 AM 0
2:00:00 AM 0
3:00:00 AM 0
4:00:00 AM 0
5:00:00 AM 0 O/T 0

Date 8.2.11 11.5
Tue Hours Day H Night H
6:00:00 AM 0 11.5 0

I have to admit that I am a bit lost as to how the data you are using is set up and what everything may or may not mean. I have been away from the site for some time and I am not sure how they are approaching the display of sheets these days. It looks as though they have something they are testing that is designed specifically for this site based on the 2 threads

http://www.mrexcel.com/forum/showthread.php?t=89356
http://www.mrexcel.com/forum/showthread.php?t=399380

I don't know if there is an attachment feature here either, I seem to remember there not being one before, but as I said I have been away quite some time.

Sorry I can't be of more help right away. I wish I could grasp what your data looks like, probably also a product of me being away for a while, I am not used to envisioning this stuff any more. Perhaps someone else can be of some assistance more quickly or I will check back to see if we have had any movement on the thread.