Log In
Register
Home
January 24, 2020
Offline
Hi.
I'm looking for help with a simple calculation, iv started on a new contract 48Hrs a week, Monday to Saturday dinner. overtime payment after 12:00 hrs sat. x 1.5 after 48HRS Double time after 12:00hrs Saturday.
Problem iv got my start times are not fixed (irregular)
Saturdays if I start at 6:00 and finish at 13:00 My calculation shows 6 hrs plus 1 Hrs double time. TRUE
If I input 04:00 start time 13:00hrs finish time it wrong
Any help would be most appreciated, my knowledge is limited.
See Answer
Trusted Members
December 20, 2019
Offline
If i am understanding correctly you get over time at 1.5 hours for anything over 48, but also anything over midday on saturday is double time?
If so you could havein the x2 cell
=IF(G13>TIME(HOUR("12:00"),MINUTE("0"),SECOND("0")),G13-TIME(HOUR("12:00"),MINUTE("0"),SECOND("0")),"")*24
which gives you the overs worked past midday saturday
and then in the 1.5 cell
=IF(H15-N13>48,H15-N13-48,"")
Let be know ifi have understood correctly
Purfleet
Answers Post
January 24, 2020
Offline
HI Purfleet.
Thankyou awesome calculation for the sat over time, couple of things if you have the time to help just spent 2 hours and failed.
when I add start and finish for Sunday its i.e. 06:00 to 07:00 its adding an hour to the 1.5 box Sundays is all Double time.
When I clear my start time and finish time its leaving a (value ) in the adding up boxes.
Hope you can help.
Jonathan.
Trusted Members
December 20, 2019
Offline
I think you just need to sum N13&N14 instead of n13 in the above formula
So =IF(H15-N13>48,H15-N13-48,"") becomes =IF(H15-SUM(N13:O14)>48,H15-SUM(N13:O14)-48,"")
For the #value errors put a IF(G13="","", in front of the formula in n13 so it becomes
=IF(G13="","",IF(G13>TIME(HOUR("12:00"),MINUTE("0"),SECOND("0")),G13-TIME(HOUR("12:00"),MINUTE("0"),SECOND("0")),"")*24)
Purfleet
January 24, 2020
Offline
Thank you
works a treat I start my new contract today and these time sheets are scanned to my employer weekly
again Thankyou iv a lot to learn.
Jonathan.
Trusted Members
December 20, 2019
Offline
No problems - good luck with the job.
Will be trying to get up to Skeggy with the dogs at some point
January 24, 2020
Offline
Hi Purfleet.
I've used the sheet you sorted for me, still got a few problems with it and cant sort my self ( I've tried )
I uploaded this week hours I've worked ( plus sat ) yard work.
if it helps.
Contract 48 hours Monday to Sat Dinner.
If I work 48 hours in lets say 4 days I get paid 1.5 their after.
Double time after 12:00 hrs sat
Double time Sunday.
Or its back to the Pen and paper,give me a shout when your next in skeg.
Jonathan.
Trusted Members
December 20, 2019
Offline
I think it was the order of the sum that caused the problem.
=IF(G13="","",IF(G13>TIME(HOUR("12:00"),MINUTE("0"),SECOND("0")),(G13-TIME(HOUR("12:00"),MINUTE("0"),SECOND("0")))*24,""))
I have tested this with less than 48 hours, saturday past 12 and with a sunday
Give it a try and let me know
Purfleet
1 Guest(s)
