In a sheet, like the example, I have monthly shift schedules for employees. What I want is to count the total shifts for Saturday and Sunday, separately Night (N) and Morning (M)/Afternoon (A) shifts, as in columns with red.
ABCDEMNOPQRAGAHAIAJ1JUNE 20132DATE1234121314151617....
HOW MANY "N" HOW MANY "M" AND "A"3SATSUNMONTUEWEDTHUFRISATSUNMON....
AT SAT-SUNAT SAT-SUN4Employee1NNNNNMM--A205Employee2MMMM-NNNNN246
Employee3AAAAM--MMM067Employee4--MM-AANNM408............................................9EXAMPLE
My aim is to find the end working date for each task, as well as the next working date for the next task. The working days for this employee are only Monday (6hours), Wednesday (6 hours) and Friday (8hours).
D E F G H
Start Date Duration Completed Days Remaining Days End Date
I have table and need to take out montly total for each worker...
Now... Each hours in day have own factor. (I need total hours per day but for illustration)...
So when worker works day shift from 8:00 to 16:00 it's easy... 8 hours When works from 8:00 to 20:00 it's 8 hours + 4 afternoon hours When works from 20:00 to 8:00 it's 2 afternnoon hours + 8 night hours + 2 day hours
Aditional problem is when day intercept holliday or sunday when that factors need to be included (if holliday is at sunday then it's like holliday).
Here is some attachment:
Book1.xls
I've also added last day of previous month and first day of next month because of night shifts than need to be calulcated. Therefore correct number of hours is 168 and not 188.
Below I calculated manually those numbers wich I want to be automated...
Also.. This is table I get.. If it's easier to make it somehow else, OK by me. And any number of aditional columns is not problem...
I am a part of an construction company, I am looking to calculate Rig Pay, Truck pay and Perdiem for each employee based on an Excel Spreadsheet pulled from the Time Card Lines out of our accounting software. I am struggling terribly, and need assistance. The Calculations are as follows
Every Employee has multiple lines in the Time Cards sheet. Rig Pay, Truck Pay, and Perdiem come from the Employee file in our system
If employee id is the same, job number is the same, cost code is the same, calc rig pay * hrs If employee id is the same, job number is the same, cost code is the same, calc truck pay * hrs If employee id is the same, job number is the same,Data, and cost code is the same, calc Perdiem /day
The final result i should see how much each employee charges rig pay, truck pay, and perdiem per job, and cost code.
I have to calculate employees work hours for overtime.
in the timesheets:
A B C D E 1 ID Date Code Hours PayMethod 2 A123 4/14/14 TRN 20:00 Regular 3 A123 4/14/14 TTT 15:00 Regular 4 A123 4/14/14 TRN 13:00 <----- total for cell D2,D3, and D4 is more than 40
A B C D E 1 ID Date Code Hours PayMethod 2 A123 4/14/14 TRN 20:00 Regular 3 A123 4/14/14 TTT 15:00 Regular 4 A123 4/14/14 TRN 5:00 Regular <----- to make 40 5 A123 4/14/14 TRT 8:00 Overtime <----- 13-5=8, so i have to write down 8 here for overtime
How can I do this?
I want to make a command button for macro to perform this.
I am trying to write a command to calculate the commission for my employees. There commission is based on the spread between sale price and cost. For example:
If Profit is between $1.00 and $2.00 - commission = 15% If Profit is between $2.01 and $4.00 - commission = 20% If Profit is between $4.01 and $6.00 - commission = 25% If Profit is > than $6.00 then - commission = 30%
I am able to calculate the first level ex: =IF((C3-B3)<=2,"15%") It Displays the 15% in the formatted cell. (C3-B3 is the profit spread). How can I include the other 3 commission levels in the formula to display the correct commission % based on profit spread?
If I have the start date (say February 01, 2009) and the job will take 100 working days, when will it finish if there is no work done over weekends? I assume you can use networkdays somehow but the final logic escapes me
I need to calculate the employees incentives based on target achievement in different scales. In attachment sheet i filled the output manually but i need the actual formula .
to calculate how long a ticket is open in our system before being resolved. I don't want to count weekends, and if the ticket is 'suspended', I don't want to count that either. There is also the factor that the ticket 'un-suspend' date may be later than the ticket 'closed' date. Which is the bit that's throwing me.
So, I have the following fields
Ticket Open, Ticket Closed, Ticket Suspended Date, Ticket Unsuspended Date
A sample ticket might be (using above fields)
02/11/09 09/11/09 04/11/09 30/11/09
That 'should' equal two days (16 hours) as the Unsuspend date falls after the close date so it was suspended from the 4th until closure.
Now I want to know, in hours (8 hour day) how long that ticket took to resolve (i.e close), remembering you can't count the time it was suspended, or any time that fell over a weekend. Also not all tickets are suspended.
I am looking for formula to calculate the dates between two days (excluding the weekends) I am working in Saudi Arabia and the weekend days over here are thursday and Friday.
I wanted to calculate the difference between dates, excluding the weekends. After much thought i used =NETWORKDAYS(A1,B1)+IF(NETWORKDAYS(A1,A1)+ NETWORKDAYS(B1,B1)=2,-SIGN(NETWORKDAYS(A1,B1)),0) that seemed to be working fine. However, I had a problem today with this formula when my start day was saturday (24/05/08) and the last day was monday (26/05/08). What i want is that when i take the difference between these two days, i should get zero as answer as we are not including the weekends in calculation so the start date should ideally be the first weekday which is Monday. However, the formula is giving me 1 as an answer.
This formula works fine if i select 27/05/08 as last date in which case i get 2 as an answer which is right. In gist, what i am looking for is a formula which will give me the difference between two dates and exclude the weekends from the calculation. The last date can't be a weekend, but the start date can. Hence, whenever the start date is a weekend, the formula should take monday as a start day.
I am using the same structure to update another column, under the same sub:
If Target.Column = 5 Then '
Select Case Range("d69").Value Case "CB1W" ActiveCell.Value = DateAdd("ww", 1, Now()) Case "CB2W" ActiveCell.Value = DateAdd("ww", 2, Now()) Case "CB3W" ActiveCell.Value = DateAdd("d", 21, Now()) Case "CB4W" ActiveCell.Value = DateAdd("d", 28, Now()) Case "CB5W" ActiveCell.Value = DateAdd("d", 35, Now()) Case "NI" ActiveCell.Value = "" Case Else ActiveCell.Value = "" End Select
I would like to apply the code to the whole column but if I change Select Case Range("d69").Value to Select Case Range("d2:1000").Value I get a Type Mismatch Error (13) - (on Case "CB1W" ) - column d cells are data validation drop down boxes that get their value form a named ranged :Lead_Status. The column is also formatted as Text. (I applied the code to d69 because I have data already in the other cells which I don't want altered till I have the code working)
I would also greatly appreciate your advice on how to incorporate the WEEKDAY function to exclude weekends for the DateAdd function.
I want to calculate a date that is 28 days in the future. I don't want to exclude any days - However - if the end date falls on a weekend or holiday, I would like to push it out to the next business day.
I currently have the weekends covered, but am stumped on the holidays.
(For weekends, I am using the WEEKDAY function on a hidden sheet, and then the following 3 IF statements: IF today + 28 = Mon.-Fri., then give me today + 28. IF today + 28 = Sat., then give me today + 30. IF today + 28=Sun., then give me today + 29.
I have tried adding an additional IF statement to address a specific holiday - namely, President's Day on 2/18/08, which is a Monday - but it won't add the extra day, because I think my initial IF statement re: Monday being today + 28 is overriding it.
Im having a hard time creating the formula to calculate the min number of employees to complete a number of jobs that come in each day and need to be finished in 18 days or less.
Data that I have:
Forecast of the number of jobs for 251 days ( jobs range from 10 -80 per day) Each job needs to be finished in 18 days or less we need to staff a fixed number of employees for the whole year each employee can complete 4 jobs per day no weekends
I have a worksheet where I capture the date and time of each new entry in column A.
In the next column, it's counting up the number of hours since that entry was made. However, it's including Saturdays and Sundays in this count and I was wondering if there's a formula to calculate the number of hours excluding Saturdays and Sundays? I know that network days would count the number of working days from one date to the next, but really need hours. Is this possible?
How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates.
I am trying to calculate downtime for a Service Level Agreement.
The data that I have is the start date/time and the resolved date/time for an incident.
The data are in the format - 1/1/2008 03:32 AM. The incidents may occur at any time but downtime is calculated only business hours and excludes weekends. I may be required to exclude holidays but that is not a hard requirement right now. What I must be able to specify are the working hours.
As example(s) - if an incident is generated on 1/2/2008 4:00 PM and resolved at 1/3/2008 11:00 AM then the downtime is 4 hours. - if an incident is generated on 1/12/2008 4:00 PM (which is a Sat) and resolved at 1/14/2008 11:00 AM (which is Monday) then the downtime is 3 hours.
The above assumes working hours are 8:00 AM through 5:00 PM.
I have tried using the NETWORKDAYS and WORKDAY functions with little success.
I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
I would like to find out if a job took more than 24 hour cycle time (eg. 6:00 am to 5:59:59 AM next day). data:
A1 = received date & time (format "m/d/yyyy h:mm") B1 = completed date & time (format "m/d/yyyy h:mm")
my formula is '=if(B1-A1>"24:00"+0,"Yes","No"). The problem with the formula is that it doesnt exclude weekends nor public holidays. I couldnt formulate a solution using networkdays function......
In the attached WB - the formula, in Col. "D", seems to be too long - although returning the expected result. Could somebody lighten my eyes with a much shorter formula ?
I have 20 staff members that I need to schedule in half day shifts (AM & PM) across three different locations for the month of July.
I want to make sure there is no overlap/duplication for any one staff in a time slot. Nor do I want any one staff member to work an AM and PM shift in the same day.
Ideally, I would also like to avoid back-to-back shifts (e.g., not allow PM on Monday to be scheduled for an AM on Tuesday).
Lastly, I would like to have the number of shifts be be as equitably distributed across all of the staff members as well.
I need a macro that will take a list of 42 names and 27 locations. The names are broken down into group of shifts, 1 shift, 2nd shift and 3rd shift. We need to perform audits on the 27 locations in groups of 2 people at a time. What I'd like is a macro that would take all the names by shift and assign 2 random people to a location. All 27 locations have to be audited each week (safety), and a report filled out and turned into our safety committee. I would like to post the audit sheet with the macro on our shared drive. Can anyone help me with this.
I would like to run some code against the currently selected range of cells in the worksheet, but when I click on the VB button that runs the code, the focus shifts from the worksheet to the VB app, and the cells in the worksheet are no longer selected.
I know this is probably not a question that's come up before, but does anyone have any idea how to retain the 'selected' status of the cells in the worksheet ?
This problem started after a lot of development of various macros in a moderate sized workbook. I've gone back to various stages of development and am unable to find what is causing this one worksheet tab to jump to the beginning of the cue (to the left) when i first click on that tab.
Thought the most likely culprit was a worksheet change macro, but even after deleting that, the behaviour persisted.
I have checkboxes (from the control toolbox) in a spreadsheet next to text. When I am in the normal view everything is where it is supposed to be. But when I do a print preview the checkbox shifts and comes on top of the text that was at the left of the checkbox.
This form has to be presentable both on paper and electronically. So both views are important.
I'm currently working with MS Excel 2010 and have a document full of people with their working hours. So their start time and their end time. Now these people should get a monetary reward dependent on the different time shifts they work in. For example, someone is scheduled for a shift starting at 6:00 AM (cell A1) until 20:00 PM (Cell B1). His actual clocking-in time is 5:48 AM (Cell C1) and clocking-out time is 19:58 PM (Cell D1). There are three shift reward types:
1) Shift Regular (E1): From 7:00 AM until 18:00 PM 2) Shift night (F1): From 18:00 PM until 0:00 AM 3) Shift midnight (G1): From 0:00 AM until 7:00 AM
How can create a formula in cell E1, F1 and G1, which will first look at the actual clocking-in time and scheduled time and will say 5:48 AM is too early as you start at 6:00 AM, so 1 hour in cell G1, and 11 hours in cell E1 and 2 hours in F1 (should automatically round up 19:58 to 20:00). However, after 4 hours work someone will get 0:30 hours lunch break, which should be deducted in E1 as well. And as mentioned earlier actual clocking time should be round up to 15 minutes, so 19:53 should be 20:00 and 6:13 should be 6:15.
Also note that the cells with time are in TIME and should become numbers (e.g. 7,5 hours) in cells E1, F1 and G1.
I also added an attachment, which shows what I would like to retrieve automated in the RED boxes.
I am developing a report that tracks the efficiency of industrial sorters on an automated assembly line. The line runs around the clock in three shifts (1: 7:30am to 3:29pm, 2: 3:30pm to 11:29pm, and 3: 11:30pm to 7:29am), and collects certain performance indicators, such as units processed. I am trying to calculate the units processed during each shift on each day. For example, if a run ran from 2:30 pm to 4:30 pm, and processed 100 units, I would want 50 units credited to shift 2 and 50 units to shift 3 (we are assuming a steady rate of processing).
My data arrives from the machine with the following columns (each run is a separate row):
B|D|E|G|H Run #| Start Date| Start Time| Units Processed | End date | End Time
I have successfully used the start time to determine the starting shift with IF statements:
=IF(--E3<0.3125,3,(IF(--E3<0.6458,1,(IF(--E3<0.9792,2,3))))) and a similar formula to calculate the end shift. This works fine if the run only spans two shifts within the same day.
My problem is that some of the runs, run across more than one shift and even across days. For example, a run that ran from 10:30 pm on one day to 4:30 pm on the next, would overlap 5 shifts and two days. I need to be able to figure out the proportion of time spent on each shift, and use that to get the proportion of units processed during each shift on each day.
I am currently thinking of a convoluted series of nested IF statements (and generating lots of columns to the right of my data for each potential shift within a run), but this seems inelegant and cumbersome. Is there a more logical way to approach this? Even more specifically, is there a function that can pair my start/end dates and times with a set of shift start/end times and calculate the elapsed time for each shift within a run? Something with MATCH or VLOOKUP maybe?
We have a person who work in 24/7 support. One person work passive 24 hour, but it 24 hours are divided in to next shifts: normal 06-18, over hours 18-22, night hours 22-06.So if we have some one who is supporting client say from 17h till 23h, we have next results:
Name Start time End Time 06:00 - 18:00 18:00 - 22:00 22:00 - 06:00
John 17:00 23:00 1:00 4:00 1:00
Is it possible to in time range enter formula who subtracted end time from start time, but taking into account 24 hours?
I have an Excel sheet where users enter shift start and finish times (normal Excel time format) - for example: A1 might be 18:00 and A2 might be 06:00 for an overnight shift from 6pm to 6am.
All I need to calculate from these times are the number of hours to which an allowance applies, under the following conditions.
The allowance is paid for all hours worked between 18:00 and 06:00.After 10 hours, a shift becomes overtime and no shift allowance is paid.Shifts are regularly worked overnight (i.e. past midnight into the following morning)
Example scenarios include:03:00 - 15:00 would pay 3 hours of shift allowance09:00 - 21:00 would pay 1 hour of the allowance (as the shift becomes overtime from 19:00)03:00 - 21:00 would pay 3 hours19:00 to 08:00 would pay 10 hours (as the shift becomes overtime from 05:00 the following morning)22:00 to 10:00 would pay 8 hours And so on.
I feel like I’ve got most of the pieces of the jigsaw, but I can’t put them together! I’ve got the following formulae working out bits of what I think I need:
[Code] ........
The above works out the number of hours before 6am and after 6pm respectively (which I can then SUM), and I believe also accounts for overnight shifts. This obviously doesn't include the more-than-10-hours criteria yet.
The larger formula now looks like this:
[Code] ......
…but this still doesn’t work properly! The sections referring to 1.25 were my attempt to get the shift allowance to stop if the past-midnight shift continues past 06:00, but I don't think it works properly.
I also know I’m probably using *24 and /24 more than I need to, but that’s partly so I’ve got a better grasp of what the formula is doing.
Once this is working, I'm happy using an IF… >10 formula to prevent the total number of hours of shift allowance being more than 10. However, I’m really struggling to find one single formula that will factor in shifts that might start before 6am and finish after 6pm (i.e. 05:00 – 19:00, which should pay one hour), shifts that might go past midnight and possibly past 06:00 the next day, and so on.
Lastly – not to try and complicate things further – there is an optional cell elsewhere, say A3, where a user enters ‘Y’ if the individual takes an unpaid 30 minute break at some point during the shift. This is to be deducted from whatever type of hours are being paid at the end of the shift. For example, if a shift is from 01:00 - 11:00 with the break, it would pay 5 hours with a shift allowance and 4.5 without. If the shift was 13:00 - 23:00, it would pay 5 without the allowance and 4.5 with the allowance. Is there a practical way of doing this, or does this become much more complicated?
