# To Build A Rota For Work

Nov 12, 2009

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.

## Work Rota/Roster Calculating Hours

Aug 11, 2004

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.

## Creating Random Weekly Work Rota

Aug 4, 2006

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

## Rota Pattern - Create Table To Display Name Of Employees And Their Relevant Rota

Oct 9, 2013

I am trying to create a table to display name of employees and their relevant rota. Attached is a spreadsheet as an example of one rota I have.

The rota at the top gives the 3 week rotation, the table underneath is the names and which week they start on and what week.

Trying to create a table at the bottom that when I put someones name in and change the date it will populate his rota for that day/week.

I have loads of different rotation plans and need to have them all together when I enter a date.

## Rota - Calculate The Hours Every One Has Done Each Week

Nov 19, 2007

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've included a zip file of the excel file

## Create Monthly Rota For Up To 8 People?

Aug 3, 2014

I want to create a monthly rota for 8 people to cover monday to friday. Only one person per day and I need to put this in a calendar .

## Showing Available Times On Staff Rota

Feb 26, 2010

It is a formatting problem based on an INDEX formula i believe, but I am unsure where to start.

I have added a template where I have shown examples of what i want to achieve, the explanation is a lot clearer.

## Rota Time Calculations And Colour Coding

Sep 13, 2013

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.

## Calculating Hours In Rota That Includes Text Fields?

Jan 12, 2012

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!!

An example of my rota for you to see:

MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY

Total hours for week START FINISH START FINISH START FINISH START FINISH START FINISH START FINISH START FINISH
10:00 17:00
10:00 17:00 12:00 16:00
Lunch Hrs/Wk 18:00 #VALUE! 19:00 24:00
standby standby 19:00 24:00 standby standby o2 Cover o2 Cover
Dinner Hrs/Wk #VALUE!

## Rota - Add Up The Total Number Of Hours Per Week Per Employee

Oct 14, 2007

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.

## Pull Out Daily Staff Location Schedules From A 20wk Cycling Rota

May 18, 2006

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.

## Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames

Jan 11, 2009

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.

## Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop

Mar 3, 2009

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.

## Relating List Of Random Numbers To A List Of Names To Create Rota

Nov 21, 2013

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).

## Master Build Up

Jan 8, 2009

I have a brand Master in below format in sheet 1

brand codebrand Description10a11b12c13d14e

and Product master in sheet 2

product codeProduct Description000roof paint001floor paint002exterior paint003marine paint004wood finishes

I need in sheet 3 for each brand

Brcode + Product CodeBrand Description and Product description10000a roof paint10001a floor paint10002a exterior paint10003a marine paint10004a wood finishes

## How To Build Formula That Would Allow Remaining UOM

May 17, 2014

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.

## Build A Working Day Calculator

Dec 4, 2009

I need to create a calculator that tells me how long a invoice will take to be paid using my current processes at work, working days only.

I need to imput the date the invoice is received and then for the rest to be worked out automaticly

I need it to do the follwoing ....

## Trying To Build Automated Schedule?

Jun 23, 2014

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.

## Using Listbox To Build A Sheet?

Jul 15, 2014

The code im stuck on is as below:

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.

## Build A UserForm From Code

Dec 13, 2007

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.

## Automatically Build Out A Table

May 30, 2007

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

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).

## Need To Build Up One Dynamic Report

May 28, 2009

I need to build up one dynamic report.

1) Everyday workers fill up with records in each sheet (record count can be different, because there is a "List" table)

2) Each day is in one separate sheet (for example: 1st June is in sheet with name 1, 2nd June is in sheet with name 2 .)

3) ALL sheets are named 1, 2, 3 ... 31

4) And in the sheet with name "ALL records" must be generated all records from sheet 1,2, ... 31

Here is my Example

## Build A List To Put Into Microsoft Access

Nov 13, 2008

I am trying to build a list to put into Microsoft access

Cell A1 has "
Cell B1 has 8W###
Cell C1 has " Or

I want a formula that makes D1 "8W### Or ". edit: I got it =(A1) & (B1) & (C1)
I kept trying to use "" and it would work.

## How To Build And Execute Formulas And Functions

Dec 10, 2012

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.

## How To Build A Formula In VBA That Can Paste Into A Cell

Apr 6, 2013

I need to run a loop to look up several columns.

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.

## Build Dictionary Of Abstract Words

Oct 1, 2013

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.

## Automated Graph Build Macros

Jun 13, 2014

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
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Eeno1!\$B\$2:\$N\$3") -----this one unchanged

[Code] ......

## Build A Variable Length Array

Apr 10, 2007

I am trying to do is to build an array from a series of cells that may or may not contain text.

For example cells A1:A6 may be equal to red, orange, yellow, green, blue and purple,
but each cell may also be blank.

So I might also have blank, blank, yellow, blank, blue, purple.

Is there a way I can make an array of just {yellow, blue, purple}? and then the next time it may be {red, blue}

Or can I index only cells that have text?

In the end, I just need a way to make a selection from only those cells that have text.

## Build Range From Column And Row Values

Apr 24, 2008

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(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.

## Build Array With Days Of The Week

Aug 7, 2008

This line will give me the current weeknumber

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.