Payroll template advise

[wrightio]

Hi All,
I am hoping you may be able to help me with a problem that is driving me nuts. I spent all day yesterday trying to find guidance so that i could come up with a solution, but i'm going forward in a rudderless boat.

Here is what i am trying to solve. I work at a place where our pay is wrong most weeks, so we have to check it closely constantly. The problem is, over the course of 1 day we can cross over 2-3 different pay rates.

What i want to be able to do is setup some kind of sheet where i can input my start and finish time on a certain day and formulas that i have set in the background will determine what the pay rate is at a given hour.

eg. lets take a Monday for example - I work from 6am - 2pm.. starting at 6 am I am eligible for double time btw 6am-7am, after 7am it reverts back to the normal rate. The only other criteria comes if i don't have a break before the first 5 hours of work. If i don't, i go back to double time until i do up to a maximum of 3 hours (my 2pm finish). This scenario happens on weekdays. On Saturday and Sunday its easier, time and a half on Saturday and double time on Sunday.

In case you are wondering, this is an Australian payroll question. Its a little different to the US etc.

I have some experience with excel, but nowhere near enough to visualise the path to solve this one. I have found ways where you can define a rate table and apply the applicable rate in another larger table, but it is a lot bigger and bulkier than i would have envisaged. I'm looking for something that uses a formula in the background to keep the template clean and minimal. Mon - Friday are the same, then there is Saturday, then Sunday. I would be greatful if someone could show me the right path. thanks

 

[philaugust2004]

so to clarify:
Mon to Fri you get double time from 6am to 7am. If you work to 11am with no break you get normal time from 7am to 11am and double time thereafter. What happens if you work until 12 and take a break and then continue until 2pm?
What happens if you work past 2pm?

 

[wrightio]

so to clarify:
Mon to Fri you get double time from 6am to 7am. If you work to 11am with no break you get normal time from 7am to 11am and double time thereafter. What happens if you work until 12 and take a break and then continue until 2pm?
What happens if you work past 2pm?

Sorry, i should have mentioned that part.. what happens in the instance of no break.. I will get double time from 11am to the time i get a break. eg. if i get a break at 12pm I get double time for 1 hour (11am -12pm), if i get a break at 1pm its double time for 2 hours (11am-1pm). and so on.. then if there is remaining time until 2pm its normal time when my shift ends.

If i happen to work after 2.. or 2:06pm to be exact, then its overtime and i go onto double time until i finish. eg 2:06pm - 4:06pm = 2 hours @ double time

 

[philaugust2004]

ok
so 2:06 is the official end time? Does that make 7:06 the official start time for normal pay?
How long is the break and is it paid?
Is there a minimum time for a break to be official? ie would a 5 min break at 11am (11:06am?) mean you stay on normal time until 2:06pm?

May seem petty questions but best to get detail right to begin with

 

[wrightio]

no i get it.. the official time is 2:06pm to finish.. this is something to do with our official breaks.. only the end time is :06. Everything else is on the hour. We get one 30 minute break per day (unpaid) which makes our day 7.6 rather than 8 hours. a 5 minute break just after 11 wouldn't be officially counted.
I don't think it will be important here.. but if we don't have the break.. we claim what's called an MBNT (meal break not taken) and include the hours it wasn't taken. eg MBNT 11:00 - 13:00 (2 hours) or MBNT 11:00 - 14:06 (All day or 3 hours) . This claim means that particular time span should be paid @ double time.

 

[philaugust2004]

ok...more or less clear now.
One final point to clear it totally in m mind. (its 1am so time to sleep!)

You work 6am to 2:06 with no break so its 1 hour at double time, 4 hours at normal time and then 3:06 at double?
You work 6am to 2:06 with 30 min break at 11am so its 1 hour at double, 4 hours at normal, 30 mins unpaid and 2:36 at normal(11:30 to 2:06)?

I will look tomorrow unless someone else solves it in the meantime.

 

[wrightio]

ok...more or less clear now.
One final point to clear it totally in m mind. (its 1am so time to sleep!)

You work 6am to 2:06 with no break so its 1 hour at double time, 4 hours at normal time and then 3:06 at double?
You work 6am to 2:06 with 30 min break at 11am so its 1 hour at double, 4 hours at normal, 30 mins unpaid and 2:36 at normal(11:30 to 2:06)?

I will look tomorrow unless someone else solves it in the meantime.

pretty close.. just to clarify.. if I don't get a break from 6am - 11am.. that's 5 hours without a break, so i go into the penalty phase. from 11am it becomes double time for every hour until i have a break. OR if i don't get a break before finishing work at 2:06pm it is 3 hours at double time. 2pm - 11am = 3 hours (the :06) is not included in this instance = 4 hours at double, 4 hours at normal - 30 minute break unpaid

so using Monday as an example, a day could look like this: 6am-7am (@double time for early morning penalty), 7am-11am (normal rate), 11am - 2pm (double time for 3 hours). If i take the example where i get a break before 11am, then the break down looks like this: 6am-7am (@double time for early morning penalty), 7am-2:06 pm (normal rate) = 1 hour of double, 7 hours of normal - 30 minute break unpaid

thanks for your interest

 

[philaugust2004]

A first try. Have a look and see if it does as expected. There were more variables than I initiaily thought!. It is just a basic working sheet for now with no fancy formatting and I am sure some formulas can be made better.


Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

start time

*

*

*

normal pay per hour

*

*

*

before

double time

*

start break

*

*

*

hours to be worked before double time kicks in

*

*

*

after

double time

*

finish time

*

*

*

hours worked before break

*

*

*

weekends

*

double time

*

*

*

*

*

*

total hours

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

rate

hours worked

pay per hour

total

*

*

*

*

*

*

*

*

*

*

double time before 7

*

*

*

*

*

*

*

*

*

*

Normal time

*

*

*

*

*

*

*

*

*

*

double time

*

*

*

*

*

*

*

*

*

*

total hours

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:150px;"><col style="width:131px;"><col style="width:90px;"><col style="width:100px;"><col style="width:64px;"><col style="width:64px;"><col style="width:178px;"><col style="width:64px;"><col style="width:64px;"><col style="width:214px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]6:00 AM[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]7:00 AM[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]11:00 AM[/TD]

[TD="align: right"]5:00[/TD]

[TD="align: right"]2:06 PM[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2:06 PM[/TD]

[TD="align: right"]5:00[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]7:36[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1:00[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30.00[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]6:36[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]99.00[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]0:00[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0.00[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]7:36[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F3 =TEXT(IF(B2="","",B2-B1),"h:mm") F4 =IF(B2="",TEXT(B3-B1,"h:mm"),TEXT(B3-B1-0.5/24,"h:mm")) B8 =TEXT(L1-B1,"h:mm") C8 =+F1*2 D8 =+C8*B8*24 B9 =TEXT(IF(B2="",(B1+5/24)-L1,IF(B2-B1>F2,F2-B8-0.5/24,B2-L1-0.5/24))+IF(B3-B2+0.5/24>F2,999,B3-B2),"h:mm") C9 =+F1 D9 =+C9*B9*24 B10 =TEXT(IF(B2="",TEXT(B3-B1-B9-B8,"h:mm"),TEXT(B3-B1-B9-B8,"h:mm")-0.5/24),"h:mm") C10 =+F1*2 D10 =+C10*B10*24 B11 =TEXT(B10+B9+B8,"h:mm") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[philaugust2004]

File is here (I hope)

https://www.dropbox.com/s/vssmtfo270dykat/pay.xlsx?dl=0

 

[wrightio]

Thanks Phil. I have the download from your dropbox. I will give it a run through and see if it will do the trick. I appreciate your help on it