Calc: how to work out what I'm being paid

December 18, 2008 12:40 PM

I need some help finding/creating a Calc/Excel formula that will help me track my breaks at work, depending on how long I work per day.

I work shift of varying different lengths. One day I might work 3 hours, 7 the next and 4h30m the following day. I get a break time dependent on how long I've worked, which means I'm not paid for that period of time.

For example, if I work 3 hours, I get paid for 3 full hours. If I work 7 hours, I get a 45 minute unpaid break, which means I only get paid for 6h15m. If I work 4 hours, I get a 15 minute unpaid break, which means I get paid for 3h45m.

I want to be able to track in Openoffice Calc (by preference, though Excel will do) what hours I'm at work & what hours I get paid for, every week. I've uploaded a working copy of the spreadsheet here.

What formula do I need to put into F2, F3, etc, to work out how many hours I've been paid, and then return that number as a percentage (so I can easily multiply that number by my hourly wage, and get an idea of how much I'll actually be paid)?

I hope this is clear. I don't use spreadsheets very often.

posted by Solomon to computers & internet (7 comments total)

What are the cutoffs for breaks? I'm assuming :
less than 4 = no break
4 up to 7 = 15 min break
7 or more = 45 min break

If this is correct, you need =IF(E2<4>7:00;E2-0:45;E2-0:15))

What this is saying is:
If E2 is less than 4, F2=E2
otherwise,
if E2 is greater than 4, F2=E2-45 min
otherwise
F2=E2-15 min
posted by chrisamiller at 12:55 PM on December 18, 2008

I'm unclear on the guidelines. Are the examples you've given the points at which your break time changes? If you work less than 4 hours, no break. Work 4 to 6:45 and you get :15, and work 7 or more and you get :45? If so, you want a nested IF formula in Excel.

=IF(A1<3.25,A1,IF(A1<7,A1-0.25,A1-0.75))

This is assuming you always work in 15 minute increments. Hopefully that will translate to Calc easily. That will get you how many hours you are being paid for versus how many you are working+breaking. To get the percentage, divide the paid hours by the working hours.
posted by soelo at 12:55 PM on December 18, 2008

Note: this works in openoffice calc, and I don't think there are any tweaks needed to make it work with Excel. If there are, I'm sure someone else will correct me.
posted by chrisamiller at 12:57 PM on December 18, 2008

Sorry, my first post got mangled by the HTML filters:

=IF(E2<4:00;E2;IF(E2>7;E2-0:45;E2-0:15))

posted by chrisamiller at 1:00 PM on December 18, 2008

Well, others may have more elegant solutions, but I would first convert everything to minutes. Then do a check to see how long your break is, based on the total minutes worked.

Your post indicates that at 420 minutes (7 hr) you take 45 min unpaid break. (375). for 240 minutes, you get 15 unpaid.

So, I would use col F as Total minutes worked. Col G as break minutes, and Col H as the difference between them

First I'd turn off the special formatting in cells C and D to make them numbers. The formatting looks nice, but can complicate the formulas or introduce odd formatting to get what you want.

Second, when you input your hours, I would use decimal notation, rather than 11:45. do 11.75 instead. (someone else can explain how to use the spreadsheet to do time operations)

so, once you change the formatting above, you will have:
C2 = 10
D2 = 14
E2 = 4

Now use the following formulas
F2 = E2 * 60
G2 = if(F2>=420,45,if(F2>=240,15,0))
H2 = F2 - G2

You don't need to convert the number to a percentage

Just put your hourly wage in cell J1. let's use 20 $/hr
in I2 you can do:
I2 = H2/60 * $J$1


Here's a comma-separated file of the formulas (I omitted Row 1)

Mon,39448,10,14,=D2-C2,=60*(D2-C2),"= IF(F2>=420,45,IF(F2>=240,15,0))",= F2 - G2,= H2/60 * $J$1,
Tue,39449,10,13,=D3-C3,=60*(D3-C3),"= IF(F3>=420,45,IF(F3>=240,15,0))",= F3 - G3,= H3/60 * $J$1,
Wed,39450,10,17,=D4-C4,=60*(D4-C4),"= IF(F4>=420,45,IF(F4>=240,15,0))",= F4 - G4,= H4/60 * $J$1,

Here's the corresponding output:

Mon,01/01/08,10.00,14.00,4.00,240.00,15,225.00,$75.00,
Tue,02/01/08,10.00,13.00,3.00,180.00,0,180.00,$60.00,
Wed,03/01/08,10,17,7.00,420.00,45,375.00,$125.00,
posted by johnstein at 1:12 PM on December 18, 2008

VLOOKUP in Excel is perfect for this.

In column A, put the times you have worked, with minutes in decimals (i.e. 6h15m is 6.25)
In column B, leave this blank
In D1, put 'Hours', and in E1, put 'Unpaid Time'
Under Hours, put each time that the amount of time off breaks:
0
4
7

or whatever.

Under Unpaid Time, put the amount of time you don't get paid for, expressed in decimal
0
.25
.75

If Cell A2 contains your first entry for number of hours there, put the following in B2:
=A2-VLOOKUP(A2,$C$2:$E$5,2)

Where C2 is the first entry in the left hand part of that mini-table, and where E5 is the lowest right entry in that mini table.

This should calculate correctly for you. If you have any questions, me-mail me, and I'll do you up a spreadsheet and send along. It keeps out all these damn nested IF's.
posted by deezil at 2:24 PM on December 18, 2008 [1 favorite]

Forgot about the percentages, mate, but those can be easily added as another column, C, that would just have time =(b2/a2) from my example.
posted by deezil at 2:28 PM on December 18, 2008