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

View 4 Replies
ADVERTISEMENT
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.

View 1 Replies
View Related
Jun 27, 2006

Is it possible to select a number of cells that are say yellow when blank but once someone type "N" or "No", they change to blue and if "Y" or "Yes" they change to green. I tried conditional formatting but it does not change the blank range to yellow until I have entered something into it.

View 6 Replies
View Related
Aug 12, 2009

I am making a time sheet for work. It should be very simple but cant get it to work. I want time in, time out, lunch column, and Total. I don't want a lunch in and a lunch out though just a section where I can put my time in :30 minuets :45 or what ever. I also want it to work. on a 12 hour clock AM/PM .

Here are my column's

B2 Is Time In

C2 is Time out

D2 is Lunch time ( decimal ? )

E2 is the TOTAL with lunch deducted.

View 9 Replies
View Related
Feb 9, 2010

if you look at attachment, in Cell J2, if cell I2 is greater than 2 minutes but less than two minutes, the I need the value of I2 in Cell J2 otherwise 0:00:00 then K2 between 3 & 4 Mins and so on.

View 5 Replies
View Related
Feb 6, 2010

Attached is a layout that I am trying to get working.

the 1st tab is the input sheet, I want to be able to post input on that sheet and have it transfer to the other tabs in the appropriate fields.

I'm having a problem mostly with the vlookup to get the data into the right cells on the other tabs.

View 2 Replies
View Related
Aug 14, 2006

I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment. The objective is to end up with a labor cost "per unit" for packing punnets of soft fruit.

The source data I have is;

1 - Start time and end time of the job

2 - Any breaks taken during the job

3 - The number of staff it took to do it

4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates

5 - The number of punnets packed.

With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!). My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong. I have attached my early draft for reference, in the example shown I am showing that a supervisor earning £7.50 an hour worked for 1 hour 15 mins at a cost of £0.39

View 2 Replies
View Related
Jul 17, 2014

I have a start time and a stop time, then a difference is calculated in cell L53. I want to subtract the time value of a break but it only works if the entered value is greater than 1:00. I tried K2-I2-L55 and It works but It wont work for times less than 1:00. I need to be able to subtract :15, :30 or any other value in cell L53.

View 4 Replies
View Related
Dec 10, 2013

1st problem concerns entering data as text and it being converted to time. I have found the formula 00:00 which does an excellant job of converting. However, when I want to subtract, (=a2-a1) for example, it treats the time as a number. Sometimes it works, but if the hours are different, say 23:30- 22:10, it will subtract 2330-2210! Is there a way to make this work?

2nd problem, related to the first is when I try to avg a column of times, I get a similar effect.

View 1 Replies
View Related
Feb 12, 2014

I am trying to track the time the various activities take. I need this in hours:minutes - Monday through Friday. Attached is the spreadsheet that does NOT work. An "x" in the column creates the timestamp in the adjacent column. Some of my formulas are not correct as the calculations do not create the correct data.

View 1 Replies
View Related
Nov 28, 2013

i have a tab on the bottom that is labeled "Man hours 2013" i need to pull the information from this sheet and create a new tab that will pull the property name, how many weeks of service, and the total man hours for each property. i will then be adding a column were i enter the monthly price for this contract divided by the number of weeks we serviced to figure out our percentage of gain/loss.

View 11 Replies
View Related
Feb 21, 2012

I've got a userform which I'm developing (my first) and I have two textboses:

Textbox6 = start time & Textbox7 = end time & Textbox10 which contains the calculation (Textbox7 - Textbox6).

Now I have code that works great for normal numbers however I need to be able to make the calculations in TIME (hence the start time / end time).

How I can amend this code to be able to calculate total time between textbox 7 and Textbox6.

Private Sub TextBox6_Change()

If TextBox6.Value = "" Then Exit Sub

If TextBox7.Value = "" Then Exit Sub

TextBox10.Value = CDbl(TextBox6.Value) - CDbl(TextBox7.Value)

[Code] .......

View 3 Replies
View Related
Feb 27, 2012

If I want to make a formula for a time such as

I have this time like 4:30 hour and i want to get this time by minutes like 270 minutes.

View 1 Replies
View Related
Oct 17, 2007

I have costs per minute and call times in seconds

How do I work out the cost per call in Excel?

It's probably something very simple, but I just can't work it out. No matter what I try, I can't get it right

View 9 Replies
View Related
Sep 28, 2009

I have tortured the cells into producing the output I want...

Namely time(s) in and out, with the hourly totals - subtracting 1/2 hour

for shifts over 6 hours.

I however have a couple of questions about glitches the approach I am using give me.... I will outline as best I can: wish I could upload a sample...

1) the user inputs (in the time format) the starting and ending times for the individual ie. 9 A = 9:00 AM ... 1:30 P = 1:30 PM simple

2) after the user inputs the total formula for each day is calculated:

=IF(D3="","",(IF((D3-C3)>.25,((D3-C3)-0.020833),D3-C3)))

That leaves the cell null if there is no time in the day...

If there is time, checks to see if more than 6 hours...

If so, deducts 1/2 hour, else does the difference calculation

This does work, but is it the elegant or simple way? recommend?

3) At the bottom of each day totals are calculated by this :

=Text((SUM(E5:E36)),"[H]:mm")

This also works to sum the individual totals to the bottom

But is this the best way to work with the times?

4) the individual then inputs the Actual Hours worked by the team...

And the sheet does a variance calculation between actual hours

Worked vs. Scheduled hours...

=TEXT((D38-D39),"[H]:mm")

Now this is a problem...cannot do negative times....need help!

5) Am having a problem with totaling the individuals weekly total using ...

=TEXT((E3+I3),"[H]:mm")

View 9 Replies
View Related
Sep 22, 2004

I need to calculate the total hours worked for a series of 22 locations. Each Location has weekly allocated hours ranging form 0 to 80.

For example:

User1 worked 4 Hours on Monday at Location1 and 4 Hours at Location2.

User2 worked 4 Hours on Monday at Location1 and 4 Hours at Location3.

I am assuming I will need to use VBA for this calculation.

View 9 Replies
View Related
May 27, 2014

how to calculate averages and standard deviations based on different time periods without having to manually change the cells?

example:

1st average output at z3, 1st std dev output at z4

data to calculate from c3:c50

2nd average output at z5, 2nd std dev output at z6

data to calculate from c51:c98

3rd average output at z7, 3rd std dev output at z8

data to calculate from c99:c148

and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?

View 6 Replies
View Related
Jan 4, 2014

Please find attached a daily copy of a spreadsheet that is used to monitor train running times.

The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.

The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.

Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.

For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)

What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.

To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.

My problem is two fold.

Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.

Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.

DAILY TRAIN RUNNING.xlsxâ€Ž

View 3 Replies
View Related
Jun 4, 2014

I am creating a spreadsheet in Microsoft Excel 2007 which holds information about the airline Emirates which I have created using information from the internet. I am currently creating a sheet which has all their flights in and holds information about the departure time and arrival time of the flight and the type of aircraft ect. I am wondering if there is anyway in being able to have the cell which has the flight number in to change colour (Green or Red) if the flight is in the air or not by using the departure and arrival times already set on the sheet. I am wondering if you possibly need to have a live time on the sheet so that it can work with that .....

View 1 Replies
View Related
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.

View 9 Replies
View Related
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

View 14 Replies
View Related
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 .

View 2 Replies
View Related
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.

View 2 Replies
View Related
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.

View 7 Replies
View Related
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

View 9 Replies
View Related
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!

View 5 Replies
View Related
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.

View 9 Replies
View Related
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.

View 8 Replies
View Related
Oct 3, 2012

I would like it to loop through all the charts on the "Graphs" sheet.

It works well but gives a "Run-time error '1004'" Application-defined or object-defined error afterward that I would like to get rid off.

VB:

Sub Colour_Chart_working()

Dim rngColors As Range

Dim rngCell As Range

[Code].....

View 1 Replies
View Related
Feb 13, 2014

Is it possible to search Excel by cell colour, i.e. font colour...and then change the said colour to another one?

View 3 Replies
View Related