Time sheets

[Guest]

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

 

[Guest]

If time worked is in A24, does:

=(A24<=8)*A24+(A24>8)*(A24<=11)*(8+(A24-8)*1.5)+(A24>11)*(12.5+(A24-11)*2)

match you requirements for equivalent hours?

 

[Guest]

This is a bit overwhelming for me. I think I should have just asked for a
formula relating to Monday-Friday work. Would you work it out on that basis
for me please. I'm trying to put an IF statement together, but I'm still
struggling.

Kind regards

Driver

 

[Guest]

I'm currently trying the following IF statement
Assuming, as you did A24 are where hours worked are
Logical Test: A24>8
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:30>8
2.) How do I get Excel to interpret "hours" as numbers. Hours is just a
heading for "hours worked"

Driver

 

[Haukwa]

Driver,

I played with your challenge. I found that the algebraic functions
performed on numbers formatted as HH:MM yield number of days in decimal
format. Therefore, if you test 10:30>8, Excel sees 10.5/24>8, giving
you FALSE.

I worked up a sample spreadsheet that includes the functionality of
determining the day of the week, when provided with the calendar date.
Let me know if you are interested in seeing/receiving the spreadsheet.

Gerry

 

[Guest]

I would certainly be interested in seeing/receiving your spreadsheet

Your help is most appreciated

If you would like to send a sample you can e-mail it to
(e-mail address removed).

Thank you in anticipation

Driver

 

[Guest]

I apologize for this slow reply. In A24 I assumed the raw hours worked (with
the lunch period already removed. I entered the following in A24 and the
second column is the result:

A24 Equivalent Hours
8.00 8.00
8.50 8.75
9.00 9.50
9.50 10.25
10.00 11.00
10.50 11.75
11.00 12.50
11.50 13.50
12.00 14.50
12.50 15.50
13.00 16.50
13.50 17.50
14.00 18.50
14.50 19.50
15.00 20.50
15.50 21.50
16.00 22.50

These seem to match up with your samples. They are only for mid-week, not
weekends.