Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Time sheets
Hello
Here's the time sheet I'm trying to create. The standard hours of work are 8 hours per day and half an hour unpaid lunch break Operatives begin work at 8:00am and finish at 16:30 thus their 8 hour of work. They are thus paid for 8 hours work. However operatives rarely work a 40 hour week and so overtime kicks in. For the following three hours they are paid at time and a half: E.g. 8:00-16:30= 8 hours 8:00-19:30= 8 hours + 4.5 (3*1.5 been the time and a half component) =12.5 hours Thereafter pay is double time E.g.2 8:00-20:00= 8 hours +4.5 hours (3*1.5 hours) + 1 hour (0.5*2 been the double time component) =13 hours How can I create a formula whereby Excel will differentiate between the different overtime rates once the 8 hour mark has been passed? Currently my formula is set up like this, compliments of "Sajay": D2=Time began E2= Time finished F3=Lunch break G4=Hours worked D3=8:00 E3=16:30 F3=00:30 =E3-D3-F3 G4 has been "Custom Formatted" to hh:mm N.B Operatives may begin work at 7:00 working until 16:30. In this instance their overtime rate would kick in at 15:30 and be paid time and a half for the remaining hour. Finally on the occasions where operatives work on a Saturday They are paid at a rate of 1.5*hours worked for the first 5 hours and then double time thereafter On a Sunday it is straightforward 2*hours. Your help would be greatly appreciated Kind regards Driver |
#2
|
|||
|
|||
Time sheets
in the cell for the daily pay:
=if(hours8,((rate*1.5*(hours-8))+(rate*8)),rate*hours) Basically, if you have more than 8 hours on clock, excell will calculate the overtime hours at time and a half, then add pay for regular 8 hours, if there are not more than 8 hours on the day, excell will calculate the regular pay rate. Substitute hours, rate for respective cell addresses. -Dave "Driver" wrote: Hello Here's the time sheet I'm trying to create. The standard hours of work are 8 hours per day and half an hour unpaid lunch break Operatives begin work at 8:00am and finish at 16:30 thus their 8 hour of work. They are thus paid for 8 hours work. However operatives rarely work a 40 hour week and so overtime kicks in. For the following three hours they are paid at time and a half: E.g. 8:00-16:30= 8 hours 8:00-19:30= 8 hours + 4.5 (3*1.5 been the time and a half component) =12.5 hours Thereafter pay is double time E.g.2 8:00-20:00= 8 hours +4.5 hours (3*1.5 hours) + 1 hour (0.5*2 been the double time component) =13 hours How can I create a formula whereby Excel will differentiate between the different overtime rates once the 8 hour mark has been passed? Currently my formula is set up like this, compliments of "Sajay": D2=Time began E2= Time finished F3=Lunch break G4=Hours worked D3=8:00 E3=16:30 F3=00:30 =E3-D3-F3 G4 has been "Custom Formatted" to hh:mm N.B Operatives may begin work at 7:00 working until 16:30. In this instance their overtime rate would kick in at 15:30 and be paid time and a half for the remaining hour. Finally on the occasions where operatives work on a Saturday They are paid at a rate of 1.5*hours worked for the first 5 hours and then double time thereafter On a Sunday it is straightforward 2*hours. Your help would be greatly appreciated Kind regards Driver |
#3
|
|||
|
|||
Time sheets
Thanks for your help Dave but I'm still having difficulties
I'm currently trying the following IF statement Assuming F3 are where hours worked then Logical Test: F38 Value_if_true: 1.5*(hours-8)+8 Value_if_false: hours The problem is I have 10:30 hours worked in A24 and yet the Logical Test is reading this as "False"??? Value_if_true_: Comes up as Invalid as does Value_if_false I'm at my wits end! How do I get Excel to recognise 1.) Logical Test is true since 10:308 2.) How do I get Excel to interpret "hours" as numbers. Hours is just a heading for "hours worked" Driver "Dave" wrote: in the cell for the daily pay: =if(hours8,((rate*1.5*(hours-8))+(rate*8)),rate*hours) Basically, if you have more than 8 hours on clock, excell will calculate the overtime hours at time and a half, then add pay for regular 8 hours, if there are not more than 8 hours on the day, excell will calculate the regular pay rate. Substitute hours, rate for respective cell addresses. -Dave "Driver" wrote: Hello Here's the time sheet I'm trying to create. The standard hours of work are 8 hours per day and half an hour unpaid lunch break Operatives begin work at 8:00am and finish at 16:30 thus their 8 hour of work. They are thus paid for 8 hours work. However operatives rarely work a 40 hour week and so overtime kicks in. For the following three hours they are paid at time and a half: E.g. 8:00-16:30= 8 hours 8:00-19:30= 8 hours + 4.5 (3*1.5 been the time and a half component) =12.5 hours Thereafter pay is double time E.g.2 8:00-20:00= 8 hours +4.5 hours (3*1.5 hours) + 1 hour (0.5*2 been the double time component) =13 hours How can I create a formula whereby Excel will differentiate between the different overtime rates once the 8 hour mark has been passed? Currently my formula is set up like this, compliments of "Sajay": D2=Time began E2= Time finished F3=Lunch break G4=Hours worked D3=8:00 E3=16:30 F3=00:30 =E3-D3-F3 G4 has been "Custom Formatted" to hh:mm N.B Operatives may begin work at 7:00 working until 16:30. In this instance their overtime rate would kick in at 15:30 and be paid time and a half for the remaining hour. Finally on the occasions where operatives work on a Saturday They are paid at a rate of 1.5*hours worked for the first 5 hours and then double time thereafter On a Sunday it is straightforward 2*hours. Your help would be greatly appreciated Kind regards Driver |
#4
|
|||
|
|||
Time sheets
I tried sending you a sheet I have built that does this but musy be your
email is phoney, if you are interested send me your address offline and I will send it to you. "Driver" wrote in message ... Hello Here's the time sheet I'm trying to create. The standard hours of work are 8 hours per day and half an hour unpaid lunch break Operatives begin work at 8:00am and finish at 16:30 thus their 8 hour of work. They are thus paid for 8 hours work. However operatives rarely work a 40 hour week and so overtime kicks in. For the following three hours they are paid at time and a half: E.g. 8:00-16:30= 8 hours 8:00-19:30= 8 hours + 4.5 (3*1.5 been the time and a half component) =12.5 hours Thereafter pay is double time E.g.2 8:00-20:00= 8 hours +4.5 hours (3*1.5 hours) + 1 hour (0.5*2 been the double time component) =13 hours How can I create a formula whereby Excel will differentiate between the different overtime rates once the 8 hour mark has been passed? Currently my formula is set up like this, compliments of "Sajay": D2=Time began E2= Time finished F3=Lunch break G4=Hours worked D3=8:00 E3=16:30 F3=00:30 =E3-D3-F3 G4 has been "Custom Formatted" to hh:mm N.B Operatives may begin work at 7:00 working until 16:30. In this instance their overtime rate would kick in at 15:30 and be paid time and a half for the remaining hour. Finally on the occasions where operatives work on a Saturday They are paid at a rate of 1.5*hours worked for the first 5 hours and then double time thereafter On a Sunday it is straightforward 2*hours. Your help would be greatly appreciated Kind regards Driver |
#6
|
|||
|
|||
Time sheets
Not over driver dear??
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
time sheets | Excel Discussion (Misc queries) | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
How do you "Unhide" multiple sheets at one time? | Excel Worksheet Functions |