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.
This spreadsheet calculates hours worked great for first and second shift but when you enter times for third shift it goes all whacky with the outcome.
I'm trying to calculate the hours worked for both my day shift and my night shift. Day shift (thanks to search ) I have managed to figure out and worked quite well. =ROUND((E7-D7)*96,0)/4 It totals adds up the time and converts it into a decimal of hours worked.
For example Joes starts at 1100 and finishes at 1330 it returns a total of 2.5 hours worked.
However I strike a problem with nightshift. They start in the late afternnon and work thoguh into the am. I have used the same formula but it doesn't seem to work: =ROUND((K7-L7)*96,0)/4
I assume because once the clock strikes 12 it's a new day and it can't work out the maths.
Lets use the example form about but make it pm. Joe starts at 2300 and finishes at 0130 it should give me a total of 2.5 hours instead it gives me 21.5 hours
I'm currently working on a rota for which I would like the worksheet to automatically tell me how many staff members I have beginning their shift before 9am and those finishing after 7pm. I currently have this working via a very crude set of IF statements for each staff member for each day of the week, returning 1 if true and 0 if false. Then I have a sum statement at the bottom of each day. Is there a much tidier and simpler way for me to calculate this?
I've attached the worksheet. A quick note is in Q52.
I want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.
Note that the night shift carries over to the next day.
I am trying to calculate over time based on shift time.
For example: Regular shifts are between 7:00 AM to 3:00 PM (Monday thru Friday). Anything between those hours and on those days should be considered REGULAR TIME. Anything between 12:00 AM to 6:59 AM or between 3:01 PM to 11:59 PM should be calculated as OVERTIME.
Anything on Saturday or Sunday should be calculated as OVERTIME as well.
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.
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.................
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.
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.
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.
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.
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.
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
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 ...
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
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..........................
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
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).
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.
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.
