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 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.
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 ?
Attached is the file wherein I am trying to make the scheduling gantt for UAE calendar where the weekends are Friday and Saturday. I am not able to format sundays as workdays some how through my conditions.
I have columns with the dates on the top, and staff listed on the rows to the left of the speadsheet. I must have 4 H's (indicating a base location), and 4 F's (indicating another base) in the column below the date. I want to have a cell, just above the date which will highlight red if there are any less than 4 H's and 4 F's. If there are 4 H's and 4 F's in the column (indicating full staffing levels), then I want the same cell to turn green, indicating that the day is OK. The red highlight would be a quick scan for supervisors to look at and see if we are deficient on staffing, and will prompt us to look for people who can be the missing H or F.
I have a table like the one below: Day Night 2/26/2007DC 2/27/2007AB 2/28/2007AB 3/1/2007 CD 3/2/2007 CD 3/3/2007 BA 3/4/2007 BA
Each letter represents a 12 hour shift (ex. D team has 36 hours for the week). If I need coverage on 2/27/07 on the day shift, I need to find a team that is working 36 hours for that week and was not scheduled to work the shift before or the shift after. (Example, on 2/27/07 A team needs coverage. The only teams working 36 hours are D and C. C team work the previous shift. So the code would input D in a cell).
Is there a way to set the ontime application to run even when excel is turned off? I'd like to be able to refresh a data table every morning at 6:30, even if excel is not on. Ideally, if even my computer isn't on. The code below works, but only if the sheet is open to the exact worksheet.
Option Explicit Public RunWhen As Double Public Const cRunWhat = "The_Sub" Sub StartTimer() RunWhen = TimeValue("09:54") Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _ schedule:=True End Sub
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
I have a simple macro that I run when I hit "Ctr Q".
How do I ensure that this macro runs every 5 minutes automatically from the first time I hit "Ctr-Q". Currently, it only runs once and then stops.
Here's my macro for your view:
Sub Macro1() ' ' Macro1 Macro ' Macro recorded 08-09-2006 by Scott ' ' Keyboard Shortcut: Ctrl+q ' Range("B6").Select ActiveCell.FormulaR1C1 = "NOW()" Range("B6").Select ActiveCell.FormulaR1C1 = "=NOW()" Range("A4").Select End Sub
I have a scheduling problem that I'm trying to get my head around. Im sure that some kind person can give me some ideas on how to get this started. It may be extremely easy, but I just cant get it off the ground. Basically I have 15 doctors that work for me - 9 full time (38hrs/week) and 6 part time (15+hrs/week) in a general practice. We are open 7 days a week
Open Close Monday8:3019:00 Tuesday8:3019:00 Wed8:3019:00 Thur8:3021:00 Fri8:3019:00 Sat9:0018:00 Sun9:0018:00
Doctors have 5 days on and then 2 days off.
The below is the proposed daily schedule and the doctor requirements. % of booked patients for the times and % of random patients for the times are included as well as average waiting times. Booked Random Waiting Doctors 8:30 - 10:00Morning Hours55%45%30min5-7 10:00 - 13:00Increasing80%20%45min7-8 13:00 - 18:00Busy 100%01.5hr8+ 18:00 - 19:00Decreasing70%30%1hr6-7 19:00 - 21:00Night 40%60%30min5+
There are 3 types of patient visits ShortDoubleTriple %0.810.140.05 Minutes153045
58% of patients choose their doctor and 42% come in off the street and will take the first available doctor.
and finally - doctors cant work for more than 4 hours without a break. brek details are below HoursBreak <4hrs0 4 - 5hrs10min 5 - 8hrs2*10 mins - 45min lunch 8+2 * 10min 2*45min lunch
I need to create a timetable for the doctors which optimises their time based on the above details. I also need to design a data sheet for reception which will maximise the number of patients seen. Finally I need to calculate what the maximum number of patients the centre can see on an average day say monday.
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 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'm trying to come up with an Employee Shift Scheduling template. I have 6 employees total with 3 different shifts (day, swing, graveyard). I would like to not do a rotating shift. I'm trying to figure out if a 5X8 shift is better or 4X10 and I would like to come up with a weekly schedule where I can see what shift everyone is working and were I have double coverage.
I have a macro that I want to run on an hourly basis, say at 9am, 10am, etc whenever the workbook is open. This needs to be definable as I have several users that will be conducting the same activity throughout the day. In other words I want the macro to run on the hour for user 1, quarter past the hour for user 2, half past for user 3 and quarter to for user 4. Each user does have their own unique macro to run so it should (hopefully) just mean introducing a line of code to each, defining the time to run.
I have an excel spreadsheet that contains customer data exported from Quickbooks. In that file there is a column called CType (Column G) which contains the scheduling cycle for that customer. There are eleven different possible schedules, including 15xYr, 1xMo, 2xMo, EOM (every other month), EOW (every other week), EOW-S / 1xMo-W (every other week between 5/1 and 10/31 and once a month during the rest of the year), EOW-Th (every other week but must be on a Thursday), ETW (every third week), On Call, W, Q.
Although the number of records changes daily, on average there are about 950 rows of data in the spreadsheet.
I need to set up an array (?) for the values in column G starting with G2 through the last non-blank row (all records will have data in column G), and calculate the next scheduled service date based on the schedule type shown in column G and the last service date which will appear in column M of that row. The last thing I need to consider is whether there is a value in Column N, which would represent a hard-entered ‘Next Scheduled Service Date’. If I find a date in that column it needs to over-ride the calculated value. My assumption to this point is that I should use a new column T to hold the value of the calculated schedule date. There are a few other details, but rather than confuse this whole thing further I’ll stop there.
What I’m struggling with is the most efficient means of running this macro. I’ve been looking at many, many threads over the past few weeks, reading VBA Programming for Dummies (which I apparently am), and reviewing other resources.
I am wanting to have a schedule posted as 8:00am to 5:00pm to show up in a format that shows the interval every thirty minutes with either a 1 or a zero so that I can calculate each intervals total staffing. Also if a break or lunch is entered I would like it to show a zero or .5 in the field (.5 being for 15 mins and 0 for anything taking the full 30 mins).
Is there a way I can count the number of different people in a column in any one day. The same persons name may appear more than once on the same day. My table is as follows:
Column A has my list of dates running from A2 to A500 Column E has my list of workers running from A2 to A500
My table of results is in a different workbook but basically what I need is this:
Nov 1 - Count how many people worked on 1st November Nov 2 - As above Nov 3 - As Above Nov 4 - As Above Nov 5 - As Above etc
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?
So I'm having a problem trying to make this scheduling sheet.. What I did was row a would be the employee list then b,c,d and so on are mon-sun Originally in b1 I would put like 9am-5pm and c1 8-4 and so on and in row i I put the total and added a sum function but somehow it wasn't able to calculate. So then I redid the whole thing this time in row b1 i put 9am and c1 5pm and so on and again I put the total and added the function and it still came out wrong..
I need to dispatch 10 technicians across the US starting each Monday. They go to 10 different customers for the whole week. I know the distance between each technician and each customer. What I would like to do is assign each technician to a customer so that the total distance traveled for all the technicians is minimized. I know that if it was only 3 techs and 3 customers (as an example), I could build a table of all the possible combinations (3 factorial or 6 permutations), determine the distance for each permutation and select the minimum. I'm not sure that doing this method with 10 techs is feasible as the number of permutations is 10 factorial or over 3 million possibilities. Anyone see a workable solution?
I've include a spreadsheet with customers, technicians and the distances between them!
I'm trying to create an Employee Scheduler. I want my employees (1 technician per row for a total of 10 rows) in the 1st column then the monthly calendar to the right (1 cell/day). That is what you'll see when you open the worksheet. Then, down below I set up my Data Area where I input my project, start date, end date, technician (where each tech is assigned a unique number), and other data across a row of cells. This could be infinite (or at least up to the very last row).
I modified a free Project Scheduler I got from XL-EasyGantt to become my Employee Scheduler but am having some difficulties getting it to do what I want. The project Scheduler basically works by entering (on the same row) the project (or task), the start date and finish date and then the cells get automatically filled in across those dates. This allows for overlapping of tasks but I want to create an Employee Scheduler so it doesn't allow overlapping or schedule a technician to start a new project before he/she has completed the one their on.
The main function which is in each cell of the monthly calendar is as follows: =IF(OR(AND($G28>=K$4,$G28<L$4),AND($M28>=K$4,K$4>=$G28)),IF(AND($E11="x",$M28>=K$4,$M28<L$4),"x",IF($E11=".",".","..")),"")
My problem: I want to be able to look at the Main Schedule and see when each Technician is scheduled for a job (or multiple jobs) for any given month. I want the function to look in my Data Area and assign the start date and end date of a project and the corresponding technician number and fill in the date cells across from their name in the Schedule above.
I need counting the number of staff within a time period. Unfortunately the schedule structure does not allow application of the simple solutions I've found regarding this which results in this being more complicated. My example is attached. My start and end time are in the same cell. I have used the Left() and Right() functions to manipulate the times. I am unable to count the number of occurrences of between the start/stop times of all my employees in the array. The formula also needs to take into account a manually entered time on the actual day.
Basically:
00:00 if F9:F42 = time is TRUE and I9:I42 = "" then count occurrences of 00:00 in scheduled times F9:F42 + if F9:F42 = time is FALSE, then if I9:I42 = time is TRUE then count occurrences of 00:00 in scheduled times I9:I42
01:00 02:00 " "
Even when I think I've gotten close the midnight time crossover keeps throwing off.
My boss wants me to take the holiday info from SAGE for 80 employees and create a record on excel. He wants to know what holidays each employee has taken and is due to take throughout the year. As each employee works a different amount of hours and a different shift pattern, SAGE records their holiday entitlement in hours rather than days. I have attached an example of one employees details and if come up with an excel document containing similar information for 80 individuals - all starting on different dates and all having a different amount of holiday entitlement. He wants to be able to look at each employees record for the year and see not only holiday data but sick days too. I don't know where to start with this - I've thought about creating a workbook with 80 pages and create a 12 month calendar for each individual with days off marked on it?
I have a table on a sheet called Contracted Staff. Table has staff names down the side and along the top is training needed.
Training H & S. fire. General training Lucy. 5/4/12 Jeff. 5/4/12 Michael. Dawn. 5/4/12
I would like on a training summary sheet to tell me who hasn't completed the training. I know I can use auto filter to select blanks to get who hasn't done it. However I want to print every training and list on the same sheet.
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?
