Payroll template advise

wrightio

New Member
Joined
Mar 20, 2017
Messages
5
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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


*ABCDEFGHIJKLMN
1start time6:00 AM***15normal pay per hour***before 7:00 AMdouble time*
2start break
11:00 AM***5:00hours to be worked before double time kicks in***after 2:06 PMdouble time*
3finish time2:06 PM***5:00hours worked before break***weekends*double time*
4*
****7:36total hours*******
5**************
6**************
7ratehours workedpay per hourtotal**********
8double time before 71:003030.00**********
9Normal time
6:361599.00**********
10double time
0:00300.00**********
11total hours
7:36************
12*
*************

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top