Been trying to come up with a formula that I can use in cells to cost a roster.
What I want to be able to do is just input start times into cells and the hours worked is automatically costed out in another cell. The trouble I have been having is that different start times attract a shift allowance eg. before 05:00 you get a 20% loading for the whole shift, between 07:00 and 19:00 is 10% and any finishing time after 19:00 is 15%, don't know how to write the formula to do this.
1. You didn't post a sample of your work.
2. You did not describe the layout of the worksheet
3. You require 20% differential for starting before 0500, but the start times between 0500 and 0700 get nothing?
4. If one starts at, say 08:30, and works until 20:30 does he get 10% for the whole shift plus another 15% for last 1.5 hours (19:00 - 20:30)?
5. What is the base rate?
-----------------------------------
However, you don't have to use one big calculation formula. Use helper cells to calculate each of the three conditions and sum up in another cell:
=IF(StartTime<TIMEVALUE("05:00"),manhours*0.2*BaseRate,manhours) =IF(AND(StartTime>=TIMEVALUE("07:00"),StartTime<TIMEVALUE("19:00")),manhours*0.2*BaseRate,0) =IF(EndTime>TIMEVALUE("19:00"),(EndTime-TIMEVALUE("19:00"))*24*1.15*BaseRate,0)
------------------------------------------------------------------
*Also, you might check the LINKS thread for "timesheets" & overtime calculations, etc.
---
Ben Van Johnson
Thanks Leah,
What I meant to say was if you start before 07:00 you get 20% loading for the whole shift, if you start and finish between 07:00 and 19:00 you get 10%, any finishing time after 19:00 attracts 15% for the whole shift, thanks for the input.
Last edited by kymbo46; 08-07-2008 at 12:12 AM.
The formula:
seems to work, but the design of your sheet makes it very difficult to enter it by dragging, etc. I only tested it on two cells with data:=IF(G3<>"",(I3-G3)*24*$AI$2*MAX(HLOOKUP(G3,$AK$2:$AL$3,2),HLOOKUP(I3,$AK$2:$AN$3,2)),"")
cost roster(mod1).xls
---
Ben Van Johnson
protonLeah,
Thanks very much for the help, what do you think I should do with the design to make it a bit easier.
protonLeah,
I have just been testing that formula, your're right it works properly in those first 2 cells but in any other cell I get the same result no matter what, also if I paste it into a cell that should produce a zero cost( beacause of Rostered Day Off) I still get the same result, it shows the cost for a shift with a 20% loading. I'm probably doing something wrong but I don't know what.
I have deleted all of your regular day off columns to make formula entry in the pay calculation table a little easier.
The formulas test the START cells for empty, but if you want to have the RDO text in the cells, then you can test for RDO vs. empty, i.e., IF(D3="RDO"....
Created a few named ranges (baseRate, Start_Table & End_Table, to make the formulas more self-explainatory
cost roster(mod1).xls
---
Ben Van Johnson
protonLeah,
Thanks again for all your help, that works great, just one last request.
I noticed in my original post that I forgot to say that on Sat. and Sun. there is a penalty rate of 1.5 and 2 time the daily rate. Would it be possible to put that into the formula somehow?
For Saturday and Sunday, there is nothing to lookup so just replace the MAX(...) with the appropriate factor and copy down the column.
---
Ben Van Johnson
Thanks for all your help Leah, much appreciated.
I received some fantasic help with my roster costing formula from protoleah last month, hopefully she or somebody else can give one last peice of help, I have posted the latest spreadsheet here, what i need to do is to automatically add some allowances that some shifts accumulate. On Sat. or Sun. if you start before 07:00 or finish after 19:00 you get an allowance of $11.75, also if you work any overtime after or before your shift more than 1 hour you get a meal allowance of $8.98 and for every 4 hours after that you get another, on days off you receive $8.98 for every 4 hours of overtime worked.
Many thanks to any help in advance,
Kym
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks