Shift Hours Calculation
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate Pay For Shift Work With Different Rates Based On Shift Hours
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 yyyymmdd 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 ifthenelse statements for calculation seems to be wrong!! examples: start = 20080101 09:15 , end = 20080101 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333 start = 20080103 21:05 , end = 20080104 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000 start = 20080202 19:00 , end = 20080202 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 Replies!
View Related
Calculating Night Shift Hours
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((E7D7)*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((K7L7)*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
View Replies!
View Related
Operational Model  Shift Start Time Plus Amount Of Hours Worked In One Cell.
Is it possible that a cell contains both numeric and alphanumeric data and to do calculations on that? For example: if a cell conatain the value "10a" or "8.5b" etc. Would it be possible to have a column that gives me the hours worked (the numeric value in the cell) and a line that gives me the amount of people that are working on shift "a" (the alphanumeric value in the cell). Is this at all possible? Or does that require VBA/Macros and stuff (in which case this is posted in the wrong part of the forum )
View Replies!
View Related
FlyingHours Calculation
One of my administrative duties is to keep a record of all of the flyinghours completed by a group of twenty pilots. I've constructed a spreadsheet and entered all of their flying records into it. At the head of each column I have the date, aircraft type, registration number, pilot name, copilot name, other crew name, day flying, night flying, solo, dual, total captain hours, etc. Whilst that I've completed the easy part of this project and that I can transfer each individual pilots flyinghours into his own seperate logbook (by filtering and copy/pasting into another worksheet), there are three other reports that I'm required to provide: 1. To be able to list the number of flyinghours completed during the previous 7 days (for each individual pilot). 2. To be able to list the number of flyinghours completed during the previous 30 days (for each individual pilot). 3. To be able to list the number of flyinghours completed during the previous 90 days (for each individual pilot).
View Replies!
View Related
Hours Worked Calculation ....
I am trying to create a spreadsheet that auto calculates my emp. time. However I do not want to use military time. I can get it to work by =a2a1 but only if it is 8.5 and 17.5. Any ideas how I can do clock in 8:30 clock out 4:30 = 8 hours?
View Replies!
View Related
Calculation Of Business Hours
I am using the following formula to calculate business hours. =(NETWORKDAYS(R9,T9)1)*("17:00""08:00")+IF(NETWORKDAYS(R9,T9),MEDIAN(MOD(T9,1),"17:00","08:00"),"17:00")MEDIAN(NETWORKDAYS(R9,T9)*MOD(R9,1),"17:00","08:00") The business hours considered here is 8AM  5PM, Start time in R9 and End time in T9. Now the problem is its calculating the correct value when the days are same, for e.g., Condition 1 When I am giving "31 March 2009 15:00:00" as start time (R9) and "31 March 2009 23:00:00" in end time (T9), I am getting the correct value. i.e, "2:00:00" Condition 2 While giving "31 March 2009 16:00:00" as start time and "01 April 2009 09:00:00" as end time I am getting a value of "1:00:00", actually the value should be "3:00:00".
View Replies!
View Related
Removing Non Work Hours From Calculation
I am trying to work out the minutes elapsed for a call monitoring system. The hours monitored are between 05:30 and 19:00  so if a call gets logged outside of these hours then the minutes calculated will be calculated from 05:30 the same day if logged on or after midnight or 05:30 the next day if logged before midnight (ie the next 05:30).
View Replies!
View Related
CALCULATION TAKING BUSINESS HOURS INTO CONSIDERATION
I need to do an hour calculation on two cells which have dates and times in both. the first cell is a call that we get from a customer and the second is the date and time in which that call is closed by us...meaning that call is complete. I need to calculate how much time in hours did it take us to complete that call for the customer. I need this calculation to respect our business hours of Monday to Friday 8am5pm and closed on Saturdays and Sundays. here are some examples. from  2/12/2004 13:00 (thursday) to  2/13/2004 9:00 (friday) answer should be 5 hours from  2/13/2004 14:00 (friday) to  2/16/2004 10:00 (monday) answer should be 5 hours
View Replies!
View Related
Date/Time Calculation For WeekEnds/After Business Hours
What calculation would I enter in a results cell if I wanted to find the delta between 2 times in date format that repersent just the business hours of 8am5pm, therefore excluding after hours and weekends. eg. Date 1 Date 2 Result Time 23/07/07 8:00 24/07/07 14:55 15:55 So far I can't get the caluclation that will compensate for the after hours and week ends.
View Replies!
View Related
Time Calculation: Calculate How Many Hours Have Elapsed Between To Entries
I am trying to build a spreadsheet to calculate how many hours have elapsed between to entries; start time (H10) e.g. 9:15 AM and end time (I10) e.g. 12:15 PM. The formula that I am using in the calculation cell field (J10) is (I10H10+(I10<H10))*24. This formula works great till I wish to include in an IF statement. What I would like is if the total hours calculated with the formula (I10H10+(I10<H10))*24 is less than 4, return 4 (hours) otherwise the value. As well if there is no start time nor end time entered then return zero.
View Replies!
View Related
Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)
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 Replies!
View Related
Stop Vacation Hours Calculation On Vacation Day
i didn't realize is that my current funcation that calculates vacation hours... will increase after a new year. i'd rather have it not increase until they are "reset" for the new year. how do i stop the function? =VLOOKUP(DATEDIF(A8,TODAY(),"y"),$S$8:$T$10,2) basically goes to a lookup table with the caculations. PROBLEM: if a user's anniversery date passes, they may go from a 12 year status to 3 year bump... this will auto adjust the amount of vacation hours they have. if the reset button is ran to calculate the vacation hours, it might over calculate giving the user 40+ extra carryover hours. anyway i can make this vlookup stop when the current date is or has passed the anniversery date, yet has not been reset? maybe a count down timer, not sure. http://www.ozgrid.com/News/exceleva....htm#ExcelTips
View Replies!
View Related
Start Shift And An End Shift
I have a Start Shift and an End Shift time, Start Shift = 2009/11/10 09:27:06 (GMT6:0) End Shift Time= 2009/11/10 15:13:03 (GMT6: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 Replies!
View Related
Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours
In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time. Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.
View Replies!
View Related
Difference Between Dates And Times In Days , Hours Mins ( Working Hours )
The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie : Difference between 02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes =(INT(A3)INT(C6))+MAX(MOD(A3,1)MAX(MOD(C6,1))) The following displays it in the Hrs and Mins format =TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))
View Replies!
View Related
Count Hours Between 2 Times Based On Hours In Another Cell
A1 is 10 (10 hrs worked) , A2 is 10:30am (in time), A3 is 9:00pm (out time), A4 needs to be the total hours and minutes between A2 and A3 based on the hours listed in A1. What i need is a formula that will calculate the hours and minutes between the 2 times based on hours entered in A1 but that will also compensate for a manadatory 30 minute lunch that needs to be deducted from the total hours if hrs listed in A1 are more than 6. example: worked 10HRS, 10:30am to 9:00pm, Total hrs is 10hrs 30min, which should be just 10 since the lunch is a none work time and must be subtracted. If a person worked more than 6hrs, they must take a lunch. if they worked less, than 6 then they don't have to. I need a calcuation to recognize the greater than, less than factor into the equasion also.
View Replies!
View Related
Format Total Hours To Days, Hours & Minutes
1) The output of an excel duration is : 22.00:8.00:25.00 ( day:hour:minutes )  excel cannot average and work with this number format 2) resolution  =(LEFT(L2,4))+MID(L2, FIND(":",L2)+1,4)/24+MID(L2, FIND(":",L2,7)+1,4)/1440 as an array and Custom Format the cell as [h]:mm  works perfectly. Q: to be conistent, the initial reporting is dd:hh:mm and then I convert to hh:mm so that excel can process the data. How can I convert from hh:mm to dd:hh:mm so that the excel report can be consistent in presenting the data to senior management? example attached.
View Replies!
View Related
Convert Hours To Fractions Of Hours
I am attempting to convert a spreadsheet of times (listed in the format 06:15:39.62, where 06 is the hour, 15 is the minutes, 39 is the seconds, and .62 is in truncated miliseconds) into fractions of hours (so, 6.25 [NOT 6:25!]). I've so far been doing it manually for each value, which is quite tedious (doing basic division of seconds and minutes into hours, to find the fraction) but I'd like a single formula which I can then apply to the whole spreadsheet.
View Replies!
View Related
Displaying Sums Of More Than 24 Hours, As Hours.
I am trying to compute a running total of hours (from row 1) in row 2 Example................ As you can see, when the sum exceeds 24 (moving to the right across row 2) the answer resets, so to speak. Cells are formatted as time. This format *seems* incapable of recognizing quantities of hours over 24 except as days, as it were. This is obviously useful in most sorts of cases but not in this sort of case. If I simply want the aggregate number of hours expressed as such am I doomed to failure whenever the total exceeds 24? In reply to a somewhat similar enquiry elsewhere in this forum, advice was given to format a cell as Elapsed Time. I dont see such a choice in my dropdown menu.
View Replies!
View Related
Shift Key Down Arrow
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 Replies!
View Related
Delete Cells, Shift Everything Else Below It Up
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 Replies!
View Related
Data Shift Down And Duplicate?
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 Replies!
View Related
Weekend Shift Patterns
I'm trying to divide the hours between 2 given times in blocks: i.e.: monday 0600  1400 = 8 hrs 24000700 [mon  fri] = 1 07001800 [mon  fri] = 7 18002400 [mon  fri] = 0 00002400 [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 Replies!
View Related
Complicated Shift Differential
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:0023:00 Second shift differential is from 22:0007:00, However lets say you start at 14:00 I don't want it calculating the shift differential of the first one for 22:0023: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 Replies!
View Related
Insert A Value And Shift Cells Value Down
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 Replies!
View Related
Calculating Shift Loadings
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 Replies!
View Related
Verifying Employee Shift Coverage
I would like to create a formula that would verify that specific work shifts have been covered each day. The spreadsheet has already been created by someone else, so I am hoping not to recreate the wheel. The goal is to make sure that all desired shifts are covered with a result in the last cell of the column that would indicate "covered", "not covered" or even a true or false statement. As an example, an 8am shift is needed. The choices are 8A or 8ALEAD and only one of these is needed for each day. Can something be set up with conditional formatting from a master list of required shifts. such as: 7OR, 730*(for just 730 and 730LEAD), 11A, etc. This is just a snippet of what the schedule looks like: ...
View Replies!
View Related
Make Cells Shift Down Automatically
i need a button to copy the content of A8:C28 and paste in another section of the worksheet, i get the button to copy and paste the selected range, but when i copy and paste again it overides my current paste, i need it to paste my new selection underneath my previous paste.
View Replies!
View Related
Shift Cells Up If Cell Equal 0
In the range A1:Y66, for every cell whose formula returns a 0, I want it to delete that cell and shift the cells up. Here is the code I tried, but nothing happened: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column > 25 Then Exit Sub If Target.Rows > 66 Then Exit Sub If Target.Cells.Count > 1 Then Exit Sub If Target.Value = 0 Then Target.Delete Shift:=xlUp End Sub
View Replies!
View Related
Ctrl Shift Enter Not Working???
i am trying to modify and existing array formula =VLOOKUP(R2,TRIM(Codes!$C$3:$D$283),2,FALSE) but ctrl shift enter does not seem to work. has anybody else encountered this problem? FYI auto calculate is on, lookup value and table array are all formatted the same. as i said, the formula works but i need it updated for one extra row.
View Replies!
View Related
CTRL+Shift+Enter Not Working
I recently upgraded my computer. I noticed I can no longer enter array formulas. When I tried to enter an array formula using CTRL+Shift+Enter, nothing happens. I don't get any error, just nothing happens. If I do the same exact thing on my old computer, it works  formula is converted and I see the braces {} added as part of my formula. Am I missing a macro or addin? I'm using Excel 2002  same version on my old computer. Is there another way to generate an array formula besides using CTRL+Shift+Enter?
View Replies!
View Related
'shift Cells Right' Depending On Value Of Cell
I need a macro that will "examine" the text of column B for every row checking to see if the text "Proper Text" is in the cell and if not, execute this code for that cell and then move on to perfrom the same until it finds an empty cell: Selection.Insert Shift:=xlToRight Range("B2085").Select ActiveCell.FormulaR1C1 = "Click for detail image" With ActiveCell.Characters(Start:=1, Length:=22).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic Note that "B2085" above is the cell number that the script stops on to execute this code.
View Replies!
View Related
Insertshift Cells Down Macro
I want to be able to insertshift cells down based on criteria in another cell. For instance, I would like a macro that would look at column B for saturday and sunday and then insertshift cells down on the corresponding cells in column D...
View Replies!
View Related
Return The Value Of The Shift Time On Other Sheet
I have a worksheet that contains a 5 week shift pattern for workers. It is briefly laid out as: JanFebAB 111330213007001400 221230203007001400 Months Jan to Dec 09 are there and shifts are divided up to ABCDE. I need another sheet to return the value of the shift time i.e. 13302130, probably via a lookup?? In my second sheet I have the Day number, the month and the shift letter.
View Replies!
View Related
Shift Cells Down Depends On Condition Of Others
Whenever "online" appears in the "Desc" column I need to shift the matching row down (shaded area). for example, "online" appears in H2, hence, C2:E2 need to be shifted down to C3:E3 leaving C2:E2 blank. Next, H3 has "online" there, C3:E3 need to be shifted down one row so the result will look like the data on the "result" tab.
View Replies!
View Related
