#1   Report Post  
Driver
 
Posts: n/a
Default 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   Report Post  
Dave
 
Posts: n/a
Default 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   Report Post  
Driver
 
Posts: n/a
Default 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   Report Post  
Intruder 9
 
Posts: n/a
Default 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



  #5   Report Post  
Driver
 
Posts: n/a
Default Time sheets

Try sending to either or

Thanks

Tim/Driver

"Intruder 9" wrote:

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   Report Post  
sajay
 
Posts: n/a
Default Time sheets

Not over driver dear??

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using VLOOKUP with a Date and Time Charles Excel Discussion (Misc queries) 4 September 20th 05 06:38 PM
time sheets karen Excel Discussion (Misc queries) 1 July 23rd 05 01:42 PM
Time Sheet Calculation Help Needed! sax30 Excel Worksheet Functions 2 April 26th 05 08:08 PM
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) RICHARD Excel Worksheet Functions 1 March 15th 05 06:49 PM
How do you "Unhide" multiple sheets at one time? RPF2407 Excel Worksheet Functions 4 December 27th 04 03:17 AM


All times are GMT +1. The time now is 09:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"