Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)

Dec 1, 2009

I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.

I have attached a screen shot of the current page,

In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.

The shift pay is the column i am having trouble with.

I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.

View 3 Replies


ADVERTISEMENT

Calculate Pay For Shift Work With Different Rates Based On Shift Hours

Apr 11, 2008

a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!

examples:

start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333

start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000

start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000

Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................

View 8 Replies View Related

How To Calculate A Number Of Calls Logged In A Shift

Jan 7, 2014

calculating the number of calls logged in a shift, based on a 3 shift system in a day. shift 1 from 08h00 to 16h00, shift 2 16h00 to 24h00, and shift 3 00h00 to 08h00. each shift receives a number of calls based on the time the call was submitted.

View 2 Replies View Related

Calculate The Number Of Hours In A Shift After Midnight

Jan 11, 2010

how would I calculate the number of hours in a shift after midnight. What I want to say is that the number of hours after midnight on a friday shift to be taken off from friday total hours and added to a saturday shift.

View 9 Replies View Related

How To Use Vlookup To Calculate The Number Of Shift Per Week For Each Agent

Dec 19, 2013

I would like to use vlookup to calculate the number of shift per week for each agent

View 10 Replies View Related

Start Shift And An End Shift

Nov 18, 2009

I have a Start Shift and an End Shift time,

Start Shift = 2009/11/10 09:27:06 (GMT-6:0)
End Shift Time= 2009/11/10 15:13:03 (GMT-6:0)
eg. Total Time = 5.3 hrs

I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.

View 12 Replies View Related

Shift Range Of Cells To Calculate Formula By Fixed Number

Mar 10, 2009

How can I shift the range of cells I want to calculate an average from? For example I want to get the average of cells A1 to A10, so =AVERAGE(A1:A10) And next I want to get the average of cells A11 to A20, so =AVERAGE(A11:A20). But I don't want to manually change the data or delete rows all the time.Is it possible to do something like: =average(A1+10:A10+10)??

View 4 Replies View Related

Calculate An Hourly Wage

Jun 2, 2007

I am trying to build a costings sheet for shift work. The first problem I am experiencing is getting excel to split up a shift into the different bands.

For example the shift needs to be calculated on different rate bands, from 00:00 to 08:00, 08:00 to 19:00 and then 19:00 to 00:00. There is a different hourly rate for each band. Therefore if I enter a start time and end time, I need excel to calculate the total charge over the different rate bands.

For example a shift 07:00 - 21:00 would have:

1 hour 07:00 - 08:00 at Night rate
11 hours 08:00 to 19:00 at Day Rate
2 hours 19:00 to 21:00 at Night Rate

Once I have this worked out I have the added complication of different rates on Friday evening,s, Saturdays and Sundays etc. But at the moment one problem at a time !

View 9 Replies View Related

How To Get The Starting And End Shift

Apr 4, 2014

I have the problem to get the starting and the ending time in a timetable work sheet, adding the starting hour in the first cell and in the second the ending.

View 5 Replies View Related

Shift Key Down Arrow

Apr 22, 2006

1:
Does a code exist to do Shift Key_Arrow Right, Arrow Down and then select that range to Copy?

I know the arrows codes but was told by someone no code can ever exist for a Shift Key.

I need to select a range after a "Search For" but the Range is not determined by the actual Cell ranges.

My Code for Arrow keys are:

'Selection.Offset(0, 1).Select '*Right*
'Selection.Offset(0, -1).Select '*Left*
'Selection.Offset(-1, 0).Select '*Up*
'Selection.Offset(1, 0).Select '*Down*
'Selection.Offset(3, 0).Select '*Down * 3 Cells

2:
What is the macro code to insert more than 1 row ?

The code I use to insert 1 Row is :

Selection.EntireRow.Insert

Sometimes I need to insert 25 rows.

View 9 Replies View Related

Weekend Shift Patterns

Jan 19, 2010

I'm trying to divide the hours between 2 given times in blocks:

i.e.: monday 0600 - 1400 = 8 hrs

2400-0700 [mon - fri] = 1
0700-1800 [mon - fri] = 7
1800-2400 [mon - fri] = 0

0000-2400 [weekend]

I got the first 3 blocks working but got stuck with the 4th one.

It should count only those hours between saturday morning 0000 and sunday night 2400 if it concerns a weekend day. and actually these hours should not be calculated in the first 3 time blocks.

View 12 Replies View Related

Shift Hours Calculation

Jan 22, 2010

I have enclosed a sheet with the dilemma i currently face.

Ive tried multiple variations on a solution none of which have been 100% accurate.

Basically the work day is split into 3 shifts :

Days ( 06:00 - 14:00 )
Afters (14:00 - 22:00 )
Nights ( 22:00 - 06:00 )

I have a report which tells me the total time the colleague will be getting paid for and there
clock in and out times.

I need to determine which shift bracket there hours fall into based on the time bands.

Ie :

David worked 8 hours , started at 10:00 finished at 18:10 , so thats 4 hours recorded in days and 4 in afters since he worked across both shifts. the 10 minutes is not being paid so it doesn't need to be recorded.

the sheet should explain things better.

View 10 Replies View Related

How To Shift A SUM Row Down When The Above Cell Is Filled

Aug 16, 2013

I've attached a simple spread which gets across the main problem. i need the totals row (currently row 7) to drop down a cell each time the data base above has an extra row added to it its pretty much a cosmetic issue but it can be useful on hugely long spreads.

View 13 Replies View Related

Shift Colors To The Left

Jan 2, 2009

I need to change to shift the colors to the left instead of the right.

View 3 Replies View Related

Data Shift Down And Duplicate?

Oct 19, 2009

I have a spreadsheet that has 3 columns. Out of which only 2 are in use.

I have attached an xls for your convenience.

The Date column is in play and Test column are in play.

Basically I want to know if there is a way, where I can

shift a value down and duplicate (in the Test column)

eg:....

View 11 Replies View Related

Delete Cells And Shift Up

Nov 7, 2009

I have data in cells A1:FM1326 I want to delete all of the cells that say FALSE and shift the remaining cells up.

View 5 Replies View Related

Using Control + Shift + Enter

Dec 11, 2008

For Excel 2007, does this key function only work after typing in a formula into a cell? I tried pasting the formula and am unable to get the {}.

Is there some setting in preferences to allow this?

View 2 Replies View Related

Macro To Shift Rows Up And Down

Oct 12, 2011

how to write a macro where I can select a row or rows (adjacent) press a button, and the secelected row(s) will shift down one row every time the button is pressed. I also need another button with a macro that shifts the row(s) up one row at a time. The following code works for shifting selected row(s) up, but I can't figure out how to shift multiple rows down a row at a time.

Code:

Sub RowUp()
Selection.EntireRow.Cut
Selection.Offset(-1, 0).Insert Shift:=xlDown
Selection.Offset(-1, 0).Select
End Sub

View 8 Replies View Related

How To Shift Objects Of Sheet

Jan 10, 2012

I have a Master sheet linked with another worksheets with formula.

Now I want to insert 1 column in between , and when trying to insert the column in it a message sowing that " Cannot Shift Objects of Sheet"

Why this error shwoing and how can i insert any column in it.

View 7 Replies View Related

Delete Row And Shift It Up If Value In Column

May 15, 2014

How would write a macro that would delete a row and shift up if they have a value in Column A?

View 6 Replies View Related

Delete Cells, Shift Everything Else Below It Up

Feb 6, 2007

I have a problem in making a delete function/sub and i'm applying it to a listbox in userform ... i'm trying to delete a row of data in the listbox which refers to cells (A12:D12) ... and at the same row (row=12), there are other data cells (E12:H12) ... after deleting the cells all other data below the deleted cells will shift up taking over the deleted cells ...

View 9 Replies View Related

Insert A Value And Shift Cells Value Down

Feb 6, 2009

If I put a length in (G13) such as 4.44 & position from (H13) is 1. Then I want that length of (G13) to move its value (4.44) into (C15) the 1st cell to add to is (C15)

Then Move (C15) to (C16) 11.12 becomes C16
and move (C16) to (C17) and so on all the way down the C column.

When it hits the last joint (C374) position 360. I want a messageBox that says you can not insert anymore.

Basically shift the existing values down untill (C374)
I have only column C to shift the calue from (G13)

I do NOT want to delete rows!

Below is how it looks before macro:
GH12LENGTHPOSITION134.441

C14LENGTH1511.121612.221711.561811.861913.112012.872113.062211.992312.03

Below is how it will look after macro: The RED moves to (C15)
C14LENGTH154.441611.121712.221811.561911.862013.112112.872213.062311.992412.03GH12LENGTHPOSITION134.441..........................


View 9 Replies View Related

Complicated Shift Differential

Apr 22, 2009

Currently I have developed a Time sheet for employees however I am having a lot of trouble with the shift differentials right now.

First, I used an If function to say If(B7="E",B6,0) E would be the evening shift and of course this works fine if everyone worked an perfect Evening shift within the time limits however, they dont.

I'm wonderin if it is possible to have one, or multiple formulas that can do the following:

First shift differential is from 14:00-23:00
Second shift differential is from 22:00-07:00, However lets say you start at 14:00 I don't want it calculating the shift differential of the first one for 22:00-23:00.
Third shift differential is a weekend one which I have figured out it's rather simple to just have a IF function for that.

Start time is B3
End time is B4
Breaks is B5 (However, Breaks is subtracted from B6)
Hours worked for the day is B6

View 9 Replies View Related

VBA To Shift Cells To The Left

Apr 30, 2009

how can i get this

Sheet1

ABCDEFGHIJKL7WTXMF31326154RIO TINTOAUDMLWSINBFSALE 258414.13 ML W/S INT BD FD 16-Mar-0917-Mar-09-251307.75N

View 9 Replies View Related

Calculating Shift Loadings

Jan 3, 2010

I need a bit of a help here. What I'm looking for is basically a formula to calculate shift loading differences. For ex.: Total hrs worked 86. As a part timer, my ordinary hrs are 76. Whatever is over 76 comes as double time ("F").

A B C D E F
87 76 42.5 15 8 11

Thats how it looks initially. "F" is the double time hrs paid (the difference between Ordinary hrs and total hrs worked). Which means that I need a formula to automatically calculate my 20% ("C"), 50% ("D") and 100% ("E") loadings. Correct would be like this: A=87, B=76, C=42.5, D12 and E=0. So, basically this 11 hrs straight double time has to come off the 100% ("E") first and then move on to 50% ("D") and 20% ("C") if necessary. So, need a formula to fields C, D and E.

View 9 Replies View Related

Control Shift Enter In VBA

Apr 21, 2006

If ur using a formula that uses Control Shift Enter to activate it in Excel,

How do u use that formula in VBA? ....

View 8 Replies View Related

Change Working Shift To A Time?

Apr 19, 2014

In Cell A1 I have the date. In Cell B1 is the shift. There are three shifts labeled as 1, 2, and 3. Is it possible to create a rule that will convert 1 to 0700, 2 to 1500, and 3 to 2300? Below is an example of how I need the shifts converted to time (military).

[Code].....

View 4 Replies View Related

Create Sum Of Shift Total Of Whole Month?

Jun 19, 2014

I am trying to create a simple dash board using only Excel. What I am trying to do is I want to create sum of shift total of whole month.

Output
Total
Total "A"
Total "B"
Total "C"

from Below table

DateShiftJob DescriptionProd AProd BProd C
1-Jul A
1-Jul B
1-Jul C

2-Jul A
2-Jul B
2-Jul C

TILL...End of month

View 2 Replies View Related

Timesheet Calculation For Shift Workers?

Jun 23, 2014

As per attached spread sheet in the yellow highlighted cells, I am trying to input a formula to automatically calculate the hours between specific times for shift workers so I can easily calculate their penalty rates however I just cant seem to get it to work, the main issue being midnight.

View 9 Replies View Related

Formula Not Reading When Employee Shift Changes

Jan 24, 2014

I need a formula that will be able to show what shift someone is on if the shift changes, for example. if one of my employees shift changes from and 8hr to a 10 hr on january 16th 2014 then I would like the raw sheet to show the old shift, the new shift and when the new shift started (date). the index formula I have will only show one shift even if there are new dates with a different shift. the formula pulls from the sheet name editor.

i really want the shifts to be different if it was change on a certain date then from that date the employee shift will reflect new shift but still keep the previous shift prior to the day the new shift started.

I've attached a sample workbook to show.

MDT Report December 201322(FILEminimizer).xlsx

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved