Google Sheets - Calculating Wages

y2ck01

y2ck01

y2ck01

Associate
Joined
26 Sep 2009
Posts
451
https://www.dropbox.com/s/xithwb9fjcgif8v/Sheets2.jpg?dl=0

The formulae I'm using is:

Total (G): =IF(C3>11.15,11.15*12.92+(C3-11.15)*18.44,C3*12.92)+F3
Overtime (D): =IF(C3>11.15,C3-11.15,0)

Good afternoon guys,

I've made a spreadsheet to calculate and keep track of my earnings and I have the basics down. It multiplies my hours worked by my pay rate and adds a premium to give me the total for the day. It also shows over time in quarter hours like I want it to. The issue is that it doesn't seem to calculate the overtime correctly i think it's to do with the format of column C, which is set to number.

Anything over 11.15 hours is classed as overtime for me and my rate goes up from £12.92 to £18.44 per hour in quarter hours and it would be rounded down. I work 00:15 - 11:30 (11 and 1/4 hours). If I was to clock out and finish between 11:31 and 11:44 for example, it would round down to 11:30.

Instead of showing 15 minutes overtime as £4.61 it's showing it as £2.76.

Any ideas?
 
You didn't link to the sheet so I created a new version for you, I think this should calculate it correctly assuming I understand what you need.

I imagine the reason yours didn't work was because you were calculating your pay based on 60 minute hours, whereas you actually need to use decimal hours. E.g., let's say you earn £10/hour and work 1 and a half hours. 1.30*10=£13 which is incorrect. You should use 1.50*10=£15 - 50 meaning half an hour. Converting is easy - just take the same hour value but divide the minutes by 60. E.g., 6:45 (6 hours 45 minutes) = 6 hours and 45/60 minutes = 6.75.

I've done the calculation for you in the spreadsheet mind.

https://docs.google.com/spreadsheets/d/1PHnsAvS3fXd-1vVCJ1m06NnqNdsNlSSlvZm6xA2QBE0/edit?usp=sharing

You need to enter in your hours worked in the hh:mm format. E.g., 11:00, 11:15, 11:30 etc otherwise you get some weird numbers!
 
Hmm does it give you an error? It's set to allow anonymous an I can view it fine in another browser.
 
You must log in or register to reply here.
Back
Top Bottom