I'm trying to build a rota for work, with the aim to gradually make it more and more complex.
So:
First step - I have made a basic skeleton, Names going down, Monday with beginning and finish, Tuesday with beg and fin etc to Friday going across. With hours worked during the day being calculated, minus 30 mins for lunch.
=(24*SUM((D5-C5))-0.5)
Second step - Now, for instance if you begin or finish work between the hours of 11:00 and 14:00 I need to make it so that you don't loose that half an hour.
I am currently trying to create a rota / rosta for work which will show people what hours they are required to work, in a format similar to below:
NAME | SAT | SUN | MON | TUE |etc > Person A| 8:00 - 17:00 | 13:00 - 22:00 | OFF | 8:00 - 13:00| Person B| OFF | 17:00 - 22:00 | 8:00 - 17:00| OFF |
This is simple enough to create but I also need Excel to calculate how many hours each person is working in a week and the total number of hours used per day and per week. I found out that I can do this using a formula to work out hours and minutes between two times. However what I would also like Excel to do is deduct the unpaid breaks which the person is entitled to, however this depends on the times that they work, i.e. in the example above Person A would be entitled to a 1 hour unpaid lunch on Saturday, a half hour unpaid tea on Sunday and no unpaid break on Tuesday. Therefore 1 hour would be deducted off the Saturday total hours, 30 minutes would be deducted from the Sunday total hours and nothing taken off the Tuesday.
What i would like to do is, create a random weekly work rota, so I just have to go in every week and let excel randomly but fairly creat a rota the employees so that they don't argue about whose doing what and just get on with the work. : D
way to calculate the hours every one has done each week to make sure everyone has done the correct hours. I was about to start using a calculator and then realised it would take forever.
I am currently working on a rota for my work. What I would like to code is that if a shift begins before 8am and finishes after 2pm it automatically deducts an hour for a lunch break. The spreadsheet currently calculates how long a shift is excluding any calculations for breaks, then checks whether that shift is a normal working day for the staff member, and returns the additional hours the staff member worked on that day. Separately, if possible I would like the spreadsheet to colour code each cell. So if a shift starts before 9am the cell fill color to be red, and if a shift begins after 1:30pm for the cell fill color to be dark red.
I'm creating a restaurant rota spreadsheet and I need to calculate the hours for each waiter, which is easy enough. But I've got to include stand-by shifts and cover shifts into the rota for the staff and I'd like the formula to ignore the "standby" text, etc in its calculations. I know you can use the =IF(... function, but that only works with one value. As well, the standby shifts will change from shift to shift and week to week, so I need one formula that might encompass all these needs... if there is one!!
I am trying to create a rota in excel but I am struggling to get it to add up the total number of hours per week per employee.
It get a little complicated as they work split shift so they may do 4 hours in the afternoon and another shift in the evening going into the next day, example they may work 11.00 am - 3.00 pm and 8.00 pm untill 2 am.
I need to pull out daily staff location schedules from a 20wk cycling rota. and produce daily shedules for staff and more detailed for the wolves above. Attached file gives an idea of what I am trying to achieve.
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
how to build a formula that would allow my remaining UOM and it's associated cells to move up to the days remaining UOM once my time goes below, say 31 days.
I have been trying to create a macro that would create a schedule for me based on date and a product type. In columns K-N I have an IF statement that tells when a shipment would need to arrive in a row that corresponds with a date (column A is 1/1/2015 - 12/31/2015). On the next page I would like to generate a schedule that lists the dates that all of the products are needed in chronological order. I've tried to use custom functions like finding the nth_occurence but it just gets way too messy with so many shipments.
I am getting "runtime error 438. Object doesn't support this property or method".
[Code] ..........
So I have a formatted empty template listed as BUILD, It has two main parts, a left half that serves as a key and a right part that I want to populate with data.
So the first part of the code copies the key/margin then as I commented I want to start building sets for.
The second part of the code I'm trying to run a loop through user entered data stored in listbox2 grabbing each entry and pasting it in the first row of each set.
Is it possible to build a simple input dialog box with only code? I'd really like to avoid having a userform permanently in the project.
Ideally it would either display and be dismissed with code only, or be constructed as a real userform, displayed and dismissed, then deleted from the project afterwards.
I am aware of excel's built-in input dialog box, but I'd really like to get away from it's awkward keyboard functionality if it's somehow possible to do. Although that would be ideal if there were some way to make the textbox's behavior to the Home, End, Tab and arrow keys more like that of a normal textbox, but I am unaware of any such options.
I am building a table for a group of vlookups, and I have a bunch of variables that need to be defined. I need to identify each and every possible combination of about seven different terms.
E.g. this simple table:
A B C 1 red1 org1 yel1 2 red2 org2 yel2 leads to this list:
red1org1yel1 red2org2yel2 red1org2yel1 red2org1yel1 red1org2yel2 ... etc. This model is almost exactly what I am starting with, except there will be six or seven columns, depending. Also, no two variables within a given column will mix with one another, e.g. no "red1red2yel1" etc.
Rather than do this by hand, I thought there must be a programmatic solution (or even a little formula).
how to build and execute formulas and functions. My love for messing with excel started with an interest in personal financial management utilizing amoritzation tables, budget planning, paychecks, tax bases, etc., My wife sees it as strange but I love messing around with the Excel formulas. For now it's all self taught but, as stated, that will be changing.
So far, I haven't been able to find how to execute the following scenario.
For brief background, I'm attempting to identify the results of various outcomes of an individual currency trade by changing the investment size given the results of the last "x" amount of trades. Put another way, I'm running data sets on Forextester wherein I trade a historical period of time on a specific set of currencies, i.e. "Pound/Dollar." I'm wanting to know what the resulting balances would be, or would have been, if I implemented a money management strategy that increased my investment on consecutive trades when there was an "x" number of consecutive "wins" (ending account balance more than beginning) on the previous "x" number of trades. and decreased my investment when there was an "x" number of consecutive losses (ending account balance less than beginning account balance) on "x" number of trades...
So far, I've built the formula so that if I input the beginning and ending balance of a specific trade and Excel populates a "W" or "L" (Win or Loss) in the "Results:" Cell which was pretty simple. The following is one of three scenarios.
Strategy Conservative:
Start with risk 1% of Account balance (which is $10,000 for example)
The Rule: With each open trade reduce the account balance used by the amount risked on opened trade (For consecutive trades, assume current trade results in a total loss of investment. Thus, $100.00 of the $10,000 accounts means you're working with $9,900 on next trade)
The Rule: After 3 consecutive winners that make (1.8 * Risk percent used by the trade * account balance used by the trade) or greater in profit, increase risk to 1.5%
First trade Investment: $100.00 plus minimum of 80% gain. Second Trade Investment: $99.00 plus minimum of
80% gain. Third Trade Investment: $98.00 plus minimum of 80% gain. For trade number four, the investment amount would now be 1.5% of the total account balance.
After 1 loss reduce risk to 1% First Trade Investment $100.00 and you lose - size of loss doesn't matter. Next trade opens with investment of 1%.
After 2 consecutive losses reduce risk to 0.5%
First trade Investment $100.00 and you lose - size of loss doesn't matter. Next trade opens with investment of 1%. Second trade is also a loss. Thus, third trade invests 0.5% of total account balance.
After 4 consecutive losses reduce risk to 0.33%
After 2 consecutive wins that make (1.8 * 0.33%* account balance used by the trade) in profit, reset risk to 1% (Two wins = 1% investment, regardless of number of previous losses
There are two additional scenarios but they all follow the same pattern. The differences are only in the actual risk percentage. I can modify the formula as needed.
My problem is that I need to build a formula that can sum each value in this row from the columns found when I'm done looping.
And then I was planning to just use "AutoFill", because of the large amount of rows this procedure has to cover.
Can I somehow create a code that will put each column number in memory, and then when done create formula like = "=SUM(G3, I3, AF3, BB3)" or something like "=SUM(& "i1" & 3, & "i2" & 3.... etc etc
I'm a little worried for overdoing loops as they have a tendency to slow down the speed.
I'm trying to build a dictionary of abstract words. It seems that all abstract words are built from less than 30 other indefinable words. I need a macro that can generate column H. I would prefer a macro because ultimately i will probably have a database of about 300 words. What I need is a macro that will go down column G, see the word "after" then find that word in column e and the corresponding number in column f, then write that number in column H. Every word that occurs in a row in column g, should ultimately be converted into a number in column h.
My problem is that i have to create 100+ graphics (this my boss want),so after i create the first table i just copy past and change the data range and the name of the table. I want to automate this process with macros so i record a macros .
Explaining: "Eono1 is the name om my workbook.
So what i want ,the macros automaticali create new graph ,and change the data range ,(my data range is for january to december i row so i want to grab the next row from C3:N3 to become C4:N4)
Lets say i have 48 countrys (names R3C2),macros should automatically stop after c48:N48 for 2014,q48:ab48-2013 and AE48:AP48.(The last graphic is created).
Here is the macros and the exaple table with chart
Code: [URL] .......
Code: Sub macro() Range("B2:N3").Select ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Range("Eeno1!$B$2:$N$3") -----this one unchanged
I'm trying to copy a pivot table cell onto another worksheet that has a table with Month-Dates across the top in columns and left-most column has several cells, each with a named range. I get an application error with:
Sheets(shtSrc).rngSrc.Address.Copy _ Sheets(shtDest).Range(Sheets(shtDest).Range(rngDest).Row, Dt.Column) shtSrc and shtDest are Strings passed in to Sub for worksheet name. rngSrc is a Range passed in to Sub. rngDest is the Named Range of the destination cell.
All of the values get passed in; I'm just not using the range properly I think.
I'm not married to what I've got so far. Basically, as long as the pivot data gets copied at the intersection of the correct Date (Column) and Row I'll be happy.
wkNumber = DatePart("ww", Now(), vbMonday, vbFirstFourDays) But also i would like to have a dynamic array that will give me the dates of this week Currently i do that using vlookup in a table stored in my personal.xls but that is no longer possible as i have to distribute my macro and i no longer have that possibility.
