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.
View 7 Replies
ADVERTISEMENT
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
Jul 24, 2009
I can't figure out how to do a proper formula for calculating time. For instance I have one column that says "Time In", the next is "Time Out" and the other is "Lunch Time". My calculation needs to be this:
Find the total hours between the Time In and Time Out and then Minus the lunch time to get total hours worked for the day. The only way I can get this to work is using 24 Hour time format. Is there another way?
View 3 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 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
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
Jan 28, 2008
i was asked to make a fair rota and i found this 1 on this website its useful but i need to add to it. if you download this work rota you will understand what i mean
http://www.ozgrid.com/forum/attachme...0&d=1154640274. i need to find out what the formula/macro is used in this work rota ...its exactly what i need but i need to add saturday and sunday to this rota and more duty columns as their is more employees and when i click the button it randomply places employees in different positions.
View 2 Replies
View Related
Mar 26, 2013
When I enter L into B3 I want B3 to show a message.
i.e. B3 is blank, I enter L into B3, it the changes L to 0645 - 1515.
And it needs to be in time format so at the end of the roster i can add how many hours in total.
View 3 Replies
View Related
Jan 2, 2007
I am working on this for two days , but I got stuck on the last step. I have a roster for about 35 employees. Calculating the daily hrs was not a problem. But I am doing the roster for one week. And I want employee wise total of hrs worked. I am quite confused as the "sum" formula works for some totals and for others it does not, although all the cells are in the right format. I tried to change the "result" cell to "number" and multiply by 24 to get the hr total as a number, but it does not work.
for example "SUMIF(E1:E57,"rafik",H1:H57)" ( this is the formula for calculating hrs for "rafik" on monday. the result cell is in "hh:mm" format and gives me the right total.
Likewise upto sunday the totals are right. What I want to do is calculate the total number of hrs from mon to sun. This seems to be impossible. the formula =SUM(H60:AL60) in a dd:mm format does not work, even =SUM(H60:AL60)*24 in a "number format" does not work.
I have tried "excel help" , tried to change the format but nothing works. The result should be 52 hrs and I cant get it no matter what I do.
View 9 Replies
View Related
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
Aug 11, 2010
I have been working on a timesheet but the problem I have come across is calculating actual hours worked only in the core hours and any work outside the core hours is calculated in the outside hours column. A standard work day is 7.6 hours working between 8.30am and 5.00pm. However if someone was to commence work either before 6am or after 8pm this is outside of core hours. I have attached an example of my timesheet for you to see what I am talking about.
View 3 Replies
View Related
Apr 22, 2014
I am trying to set up a time sheet for staff where it calculates hours worked in and outside of core hours. I can do a calculation to work out what they have worked in and out of core hours providing that part of their time starts of finishes in core times. The problem I am having is when some one works only outside core hours. Our core hours are 6am to 6pm. The problem is when they work from say 7pm to 1am. This is irregular work paid as overtime not shift work.
View 5 Replies
View Related
Sep 10, 2007
I am looking for a funtion that will help me add the hours someone is scheduled to work. The problem I am having is the format. Here's the format: The days of the week are Mon - Sun (D3-J3). L3 is a total of the hours they are scheduled to work for the week. Cell D3 is 8-2. Cell E3 is 9-3, etc. for the rest of the week. What I am looking to see if possible is in L3 take the row, Take D3 at 6 hours, E3 at 6 hours, etc for the rest of the row, and add them together for the total hours in L3. I have attached a small example file of what I'm trying to do. The format I am looking to use for the hours is as described above.
View 11 Replies
View Related
Mar 13, 2008
I am working in call center, and I get tickets and i have to meet my Services Level Agreement (SLA) with in pre defined working hours.
My working hours are 8:00 AM to 17:00 PM, Monday to Friday. (this includes 1 hour of Lunch time which is not considered as business hour)
As per the SLA, i have to complete the ticket in 40 business hours, i.e 5 business days.
Suppose a ticket is created on 3/5/2008 10:00 AM. I want to know how much time is left for me to work on that particular ticket.
View 12 Replies
View Related
Jan 12, 2010
I want to calculate employee rostered hours or days off from an exported crystal reports. The problem isn't how to calculate the hours but to calculate accurately when formatting changes occur in the exported report. This is an example for the exported sheet data.
http://farm3.static.flickr.com/2748/...50a38e59_o.jpg
So to calculate data intially using the formula
View 10 Replies
View Related
Oct 3, 2008
I have a ROW of numbers (hours) and I need to figure the hours left. Here is an example of 6 colums, Zeros and blanks are valid entries.
C1 C2 C3 C4 C5 C6 Hours Left
16 8 0 0 0 0
16 8 4 4
8 2 1 0 0
16 0 0 8 4 2 2
8 16 10 8 8
I'm trying to get a formula that will figure out hours left. I've tried a number of times.
View 5 Replies
View Related
Dec 12, 2008
I have a spreadsheet where I record individual staff start time, end times and the length of their break. From this I caculate the hours they worked for the day.
E holds Start, F holds End and G holds Break length. Thus using the following forumula gives me the number of hours and minutes:
View 4 Replies
View Related
Jan 1, 1970
I can do the timesheet formula for adding the hours worked as follows:
Start Finish Total
08:45 17:15 8.5
However, it doesn't work when I fill in a whole week work of hours in this format:
Start Finish Total
08:45 17:15 08:30
It works on a daily basis, but when total hours exceeds 24, the formula get's all mixed up - how to I format the total column to account for every 5 minutes worked, which you can't do when converting to decimal??
View 14 Replies
View Related
Apr 23, 2008
I use this formula at work to calculate business hours from Mon-Fri:
=IF(OR(L10="",L11=""),"",(NETWORKDAYS(L10,L11,$N$2:$N$23)-1)*($Q$3-$Q$2)+IF(OR(ISNUMBER(MATCH(INT(L11),$N$2:$N$23,0)),WEEKDAY(L11,2)>5),$Q$3,MEDIAN(MOD(L11,1),$Q$3,$Q$2))-IF(OR(ISNUMBER(MATCH(INT(L10),$N$2:$N$23,0)),WEEKDAY(L10,2)>5),$Q$2,MEDIAN(MOD(L10,1),$Q$3,$Q$2)))
where Q3= business start time 8.30am
where Q2= business end time 5.30pm
thus the difference between 18-Apr-08 16:30 and 21-Apr-08 13:30 is 6 hours.
I now need to adapt this formula for another Department that also works on Saturday from 8.30am to 5.30pm.
View 9 Replies
View Related
Jun 3, 2008
This may seem like an odd request... I recently received a protected worksheet that I entered my date of birth into, and it told me how long (if I pressed F9) I had been alive in y/m/w/d/m/s etc. I was wanting to do a similar spreadsheet to show how long I had stopped smoking for, and a running total of how much I was saving, but I couldn't look at the spreadsheet for help. The stop time was 01/06/08 - 05:00am GMT, up until present, and buying cigarettes I was spending £0.00004166666 per second. Is it possible to create such formulas?
View 9 Replies
View Related
Apr 11, 2013
Just like if I put the employee's number and work time for each day,
The excel will accumulate the hours automatically somewhere in the sheet. (I don't want the total hour cover each day's work time)
Is there anyway I can do it?
View 5 Replies
View Related
Aug 29, 2009
I am working on a monthly employee work schedule that has 2 stores. All employees work at both stores and the boss wants 2 separate schedules.
I have attached what I have started, which at the moment I am stuck.
I would like to have the Row 5 total hours to always reflect (subtract) a 30 minute lunch. I know it would be easier to have another column, but the boss wants it this way,
I also need the total hours in column H to reflect the total of each day (with the subtracted lunch break). In this column I am having the figures turn red if over 40 hours and green if it under 40 hours.
With having 2 stores with the employees working at both, I need to somehow have the hours of the employees add up from one store to the other (a running total, if you will).
Lastly, if I have an employee not scheduled for a day or need to put other data such as, VAC, HOL or blank cell etc in a cell, how can I get this to work and not have the formula go nuts when it is not in a time format.
View 14 Replies
View Related
Mar 9, 2006
I am trying to work out the minutes elapsed for a call monitoring
system. The hours monitored are between 05:30 and 19:00 - so if a call
gets logged outside of these hours then the minutes calculated will be
calculated from 05:30 the same day if logged on or after midnight or
05:30 the next day if logged before midnight (ie the next 05:30).
View 22 Replies
View Related
Jan 31, 2009
I charge £45 per hour with a couple of conditions - minimum 8hr day and then 1.5T after 12hrs.
Is it possible to have a table where I put in my hours and then Excel tells me how much I should charge?
View 9 Replies
View Related
Feb 20, 2014
Example: In cells A1:A10 random number between 0 & say 20, need to sum ABOVE 8 = (calculating overtime hours)
E.g.
A1 = 0
A2 = 8
A3 = 8
A4 = 10 (giving 2)
A5 = 12 (giving 4)
A6 = 5
A7 = 13.5 (giving 5.5)
A8 = 8
A9 = 0
A10 = 16 (giving 8)
A11 = (Total overtime above 8 hours) 2+4+5.5+8 = 19.5
Need to be able to increase rows and drag across.
View 3 Replies
View Related
Mar 18, 2014
I'm trying to calculate shift working hours without using dates.
The scenario is
Cell A1 = Start Time
Cell A2 = Start Time
Cell A3 = Break Time
Basically I want the output to calculate hours worked between:-
0000 and 0600 as a total in cell A4
0600 and 1800 as a total in cell A5
1800 and 0000 as a total in cell A6
Then if the value of A5 is greater than A3, subtract A3, but if the value of A5 is less than the value of A3, A5 should be zero and the remainder of the value of A3 subtracted from A4 (or A6) depending which has a value.
View 5 Replies
View Related
May 2, 2008
I need to calculate time taken to fix a piece of equipment.
A1 B1
BREAK TIME FIX TIME
4/22/08 23:00 4/23/08 04:00
Should be 5 hours, but i can't find the formula to make it work.
View 14 Replies
View Related
Nov 27, 2012
Weekly Timesheet.xlsx
This spreadsheet calculates hours worked great for first and second shift but when you enter times for third shift it goes all whacky with the outcome.
View 10 Replies
View Related
Nov 30, 2012
I thought I had everything worked out with this timesheet but I've discovered one more problem.
Weekly Timesheet.xlsx
The total overtime hours needs to show that anything over 40 hours in the Total Regular Hours cell is overtime. And it also needs to show only up to 40 hours in the Total Regular Hours cell.
View 8 Replies
View Related
