Overtime calculations

[Guest]

I need to keep track of my work times. I just want to input the start time,
finish time and lunch break. After that I would like the sheet to calculate
the following

Total Hours worked
Total Hours worked less lunch break
Overtime after 8 hours calculated at 1.5
Overtime calculated after 10 hours at double time.
Overtime Saturday calculated at 1.5 for the first 2 hours then double time
for the remainder
Overtime Sunday calculated at double time.

I have looked everywhere for a program to do this, or a template that I can
do it with Excel, but so far no good. Can anyone tell me where I could find
this? Please bear in mind that I am not a programmer and am looking for a
ready made solution.
Thanks

 

[Guest]

I don't know of any read-made solutions, but the calculations you're looking
to do are pretty simple algebraic/logic calculations which can be
accomplished with Excel's built-in functions.

No need for programming here.

Total hours worked is just a sum of a range.
Total hours less lunch is a sum of a range less another value.

Etc.

Since your needs are so specific, I would doubt there is a ready-made
template for this, however, as I say, these are calculations that can be done
in Excel without any programming knowledge.

 

[Pete_UK]

Reserve the top row for headings, as follows:

A1: Date
B1: Start_time
C1: Finish_time
D1: Lunch (hrs)
E1: Total_hrs
F1: Hrs_worked
G1: Time_n_half
H1: Double_time
I1: Single_time
J1: Effective_time

You can enter the dates down column A to suit - it is probably easier
to just put one date in A2 then highlight A2 downwards and use Edit |
Fill | Series (date and step of 1) then OK.

Enter these formulae in the appropriate cells, each formatted as Number
with 2 dp:

E2: =(C2-B2)*24
F2: =E2-D2
G2: =IF(WEEKDAY(A2)=7,IF(F2>2,2,F2),IF(F2>8,IF(F2>10,2,F2-8),0))
H2:
=IF(WEEKDAY(A2)=1,F2,IF(AND(WEEKDAY(A2)=7,F2>2),F2-2,IF(F2>10,F2-10,0)))
I2: =F2-G2-H2
J2: =I2+G2*1.5+H2*2

You can then highlight the cells E2:J2 and copy them down for as many
days as you expect to monitor.

For each day you will then need to enter the start time and finish time
(in Excel time format, i.e. hh:mm:ss) and your lunchbreak (in decimal
hours, eg .75 for three-quarters of an hour).

Hope this helps.

Pete

 

[Guest]

Thanks for the formulas, I will try and muddle my way through them

 

[Pete_UK]

Post back if you get stuck.

Pete

Thanks for the formulas, I will try and muddle my way through them