Question Google Sheets - Calculating Wages

[agentile]

Screenshot

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. That's fine. What'd I'd like is for it to also calculate and add any overtime that I may do. I'll try and explain (please use the screenshot as a reference).

Anything over 11.15 hours is classed as overtime for me and my rate goes up from £12.92 to £18.44 per hour. Is there a way for the spreadsheet to automatically calculate and adjust the total If I go over 11.15 hours? So if say I did 11.30 hours work, column D (overtime) would show £4.61 and adjust it accordingly. What I'd like is to to simply input my hours worked and the spreadsheet to do the rest. I'm perfectly capable of doing it manually but I'm wondering if it's possible.

I hope I've made sense.

 

[Wolfshadw]

Not sure about Google sheets, but in Excel, I'd use an "IF" function in Column D. The IF function allows for two options Case True and Case False, so it looks something like this:

=IF(CASE, ValueTrue, ValueFalse)

CASE= Hours > 11.15
ValueTrue=(Hours-11.15)x18.44
ValueFalse=0

Then just add the value of the Overtime column to the Total.

-Wolf sends

 

[DSzymborski]

Can really do it with a single simple equation.

 

[USAFRet]

And I would designate 2 cells as Range.
F1 for RegPay, G1 for OTPay.

Then, in the formula:
=IF(A3>11.15, 11.15* RegPay+(A3-11.15) * OTPay, A3 * RegPay)

In case of a wage adjustment, just change the values in F1 or G1 as appropriate, and everything recaluates.

 

[agentile]

Updated Sheet

So, I'm nearly there but I don't think column C is formatted correctly. How would I format it into quarter hours? So that the extra 15 minutes overtime would convert to £4.61 for example.

My current total formula is: =IF(C3>11.15,11.1512.92+(C3-11.15)18.44,C3*12.92)+F3

 

[Ralston18]

Typo?

Provided I am following correctly there should be an * between 11.15 and 12.92 in your current formula.

IF(C3>11.15,11.1512.92+(C3-11.15)18.44,C3*12.92)+F3

How are minutes treated with respect to overtime? I.e., rounding either up or down...

For example if you work 18 minutes into the hour is that time treated as a full second quarter hour and rounded up?

 

[USAFRet]

Typo?

The Forum formatting sometimes eats a pair of *.
It thinks you want to emphasize...in this case, it turns them into italics.

 

[agentile]

It would be rounded down. I work 00:15 - 11:30. If I was to clock out and finish between 11:31 and 11:44 for example, it would round down to 11:30. 11 and 1/4 hours.

I've shared it so you can edit and play around with it. Might be easier.

Shared Edit

 

[Ralston18]

The Forum formatting sometimes eats a pair of *.
It thinks you want to emphasize...in this case, it turns them into italics.

Thank you.