Timesheets with cost ie overtime, time & half

[euwest30]

Hellppp!!

I'm doing up a timesheet which has got 3 columns. It contains "Ordinary Hours", "Time & Half" and "Double Time".

I require a formula which calculates the cost automatically. If I put in 10 hours Mondays to Friday I need Excel to calculate 7.2hrs for Ordinary Hours eg $50, and 2 hours Time & Half $55 and 0.8 hrs $59 for Double time. I'll probably need one formula in a cell and the rest I'd probably be able to adopt the formula.

On Saturday it's first 2 hours for Time & Half and 8 hours on Double Time.

Would anyone be able to help me with this?

Regards

Euwest39

[mangesh_yadav]

Ideally you should have 2 different formula for the weekdays and the weekends, but ofcourse you can combine the 2 with an if statement. For the weekdays you can use something like this:

=MIN(A1,7.2)*50+MAX(0,MIN(A1-7.2,2))*55+MAX(0,MIN(A1-9.2))*59

where A1 contains the number of hours.


Mangesh

[euwest30]

Hellppp again!!!!

Okay so I've got the weekdays formula done, thank you. But what about the Saturday and Sunday. So if I have 2 hours for time and a half and the rest is overtime, how will I be able to do up the formula.

So on my Total hours, could you tell me how the formula should be written please?

Regards

Euwest30

[mangesh_yadav]

Maybe you should post your sample data.
for the weekends, you can use
=MIN(A1,2)*55+MAX(0,A1-2)*59

Then simply add up the amount for all the days. Sorry, I had been travelling and couldn't reply sooner.

Mangesh