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

View 9 Replies
ADVERTISEMENT
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
Dec 21, 2013

Refer to the attached sheet which is Daily Schedule for employee. I need a formula to add hours worked on single day in cell C2 for Monday, E2 for Tuesday, G2 for Wednesday, and so on.

Every day we have Clockin_Clockout info for each employee as shown for employee a & b.

FYI : I am using below formula to add employee hours for the week as (formula in cell R4).

[Code] .....

View 14 Replies
View Related
Jun 10, 2014

Looking for a formula for total cost based on a list of hours for each employee and a table of rates for each employee.

Please see attached file : Formula.xlsxâ€Ž

View 1 Replies
View Related
Jan 26, 2007

I am trying to add together the total hours for a given week.The spreadsheet is used to show the total hours worked from the signing off and on times given.

CELL C11 CORRECTLY SHOWS THE TOTAL FOR ONE DAY AS 08:30 AND F11 AS 07:30. BUT WHEN THEY ARE TOTALLED IN CELL B13 IS SHOWS 1122:00 INSTEAD OF 16:00.

View 9 Replies
View Related
Dec 14, 2013

Please refer to attached sheet.

I am using the attached to schedule the employees. All I want is a formula to add up all schedule hours per week per schedule in cell R2,R3 and R4.

Book2.xlsxâ€Ž

View 2 Replies
View Related
Mar 6, 2010

I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel

i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.

The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2

2 problems

Having many multiple day / date entries are the same date x 7 days Monday –Sunday

(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time

When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate

Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours

01/03/10, 9.5 hours labor, 3 hours travel

01/03/10, 9.5 hours labor, 3 hours travel

01/03/10, 9.5 hours labor, 3 hours travel

i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals

i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007

how do i calculate weekly hours by date and weeknum ?

Total Work per day

Total Travel per day

Daily....................

View 9 Replies
View Related
Feb 22, 2009

I have what I thought was going to be a simple function to create and, after a week of getting more error messages than I've had in a year, I'm ready to throw in the towel. Situation: There are two worksheets in the same workbook [Excel 2003]. One is for "Posting" details about technicians and their work orders. The other is a "Report" that summarizes the number of workorders open, closed, etc.

In the "Posting" sheet, I have created dynamic name ranges for two columns: Technician (4-digit text field) and InDate (date field that, if blank, means that the technician has not closed this work order. My ranges are set up like this:

Technician=OFFSET(Posting!$B$2,0,0,COUNT(Posting!$B:$B),1)

InDate=OFFSET(Posting!$D$2,0,0,COUNT(Posting!$D:$D),1)

So all I need to do is to total all the blank InDates for each Technician by employee number. I've tried COUNT to SUMPRODUCT. A co-worker got it to work with using...=SUMPRODUCT((ISBLANK(Posting!$D$2:$D$65307))*(Posting!$B$2:$B$65307=4288))

His solution was to list almost every available cell in each column. I just can't help but think that the dynamic name range should be able to work but I can't get it right. I think I'll choke if I get one more "N/A" or "VALUE! error.

View 3 Replies
View Related
Mar 30, 2009

i am looking to do a table which shows time started, time finished and then a total for hours that day, then that week.

Start 08:00

End 16:00

Total 8 hrs.

How can I get the total to display as 8 hrs? not 08:00? When I change the format to "number" it shows 0.33?

View 9 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
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
Dec 2, 2013

My spreadsheet is set up so that Column A has dates and Column B has a value. How can I calculate the total number of values for each day of the week? I've tried a few formulas but they either didn't work or didn't actually take the value into consideration and just counted all the 'Mondays'. I'm not sure if that's clear enough, but if we're just looking at Mondays to simplify it:

Monday, 1 January 2000: 2

Monday, 8 January 2000: 5

Monday, 15 January 2000: 0

Mondays: 7

View 1 Replies
View Related
Sep 23, 2008

I'm trying to create a userform which calculates and records the total number of hours a user puts in, however it can not exceed 40 hrs.

Dim intProject1 As Integer

Dim intProject2 As Integer

Dim intProject3 As Integer

Dim intTotal As Integer

intProject1 = txtProject1.Text

intProject2 = txtProject2.Text

intProject3 = txtProject3.Text

intTotal = txtTotal.Text

If txtTotal > 40 Then

txtTotal = txtProject1.Text + _

txtProject2.Text txtProject3.Text

txtTotal.Text = Format(txtTotal, "currency")

Else

MsgBox ["Total exceeds 40 hours."]

End If

If you guys could help me out I appreciate it

Thanks

End Sub

Private Sub lblProject1_Click()

View 9 Replies
View Related
Jun 12, 2014

I have a base of workers which log each day their activities in hours (D01-D11). I would like a macro to sum total hours of each project (project numbers are from 320-516) and put it into the table on the left.

For example: For PERSON 1, I want that the cell I60 writes 10, and cell I74 = 6. For worker 2 (PERSON 2) would be AK60 = 3,5; AK67 = 8 and AK74 = 8.

In total where would have been 10 workers.

View 2 Replies
View Related
Jun 6, 2014

I have a base of workers which log each day their activities in hours (D01-D11). I would like a macro to sum total hours of each project (project numbers are from 320-1500) and put it into the table on the left.

For example: For PERSON 1, I want that the cell I60 writes 10, and cell I74 = 6. For worker 2 (PERSON 2) would be AK60 = 3,5; AK67 = 8 and AK74 = 8.

In total where would have been 10 workers.

View 3 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
Oct 23, 2009

I am trying to sum up the total number of hours consumed on a particular activity within a specified period.

=SUMPRODUCT(($C38:$C176>=$B7)*($C38:$C176<=$C7)*($B38:$B176=$D5))

Unfortunately, the formula above gives me the total number of activity and NOT the total number of hours within a specified period. I attached the file I'm working on as reference.

View 2 Replies
View Related
Jan 29, 2014

I have data that is added to every week. I need an equation to count how many times each employee show up each weekending.

View 3 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 30, 2007

1) The output of an excel duration is : 22.00:8.00:25.00 ( day:hour:minutes ) - excel cannot average and work with this number format

2) resolution - =(LEFT(L2,4))+MID(L2, FIND(":",L2)+1,4)/24+MID(L2, FIND(":",L2,7)+1,4)/1440 as an array and Custom Format the cell as [h]:mm - works perfectly.

Q: to be conistent, the initial reporting is dd:hh:mm and then I convert to hh:mm so that excel can process the data. How can I convert from hh:mm to dd:hh:mm so that the excel report can be consistent in presenting the data to senior management?

example attached.

View 9 Replies
View Related
May 20, 2014

I have been working on the attached spreadsheet, we have projects that our employees work on. I need to keep track of the hours spent on each. Then I need to combine this information so I can do some calculations, for example based on the total hours spent I can then work out the actual cost spend on the project versus what we have invoiced for this project, like a mini Profit and Loss on the labor only.

I created a different worksheet per employee, I would like each employee to only be able to access there own worksheet, based on some protect or share worksheet rules. I created lists of Projects and Users, so this data is the same in each worksheet. Then I would like the data from each employee (only have 3 at this stage) to collate onto another worksheet, which only I can see, so I can do my total calculations etc. Along with hours, sometimes each project might have extras, this is charged at a certain rate per employee, so on the totals page I will also use this information.

Here is what I have so far. Workings 150514 - sample for expert.xlsx

View 1 Replies
View Related
Sep 25, 2013

I am trying to create a a file that will serve as time log and dashboard report for consultants who work a maximum of two hours a day, with a strict(fixed) calendar schedule with multiple sessions. Some consultants may work one hour of a particular schedule on category A(Lead) and the second hour as category 2. How to create a sheet that sums the rates for all the categories worked on a given day without using a macro. Here is my sample data

Pay Categories:

Lead

Assistant

Admin

Pay Rates

Lead-$75/hr

Assistant - $40/hr

Admin - $20/hr

Sessions and Session Dates sample:

Session 1 - 10/8/2013 to 11/8/2013

Session 2 - 11/15/2013 to 12/15/2013

Session 3 - 01/06/2014 to 03/15/2014

*Note the dates will or should be listed in separate columns in order for them to be used for daily logging.

Employees (Sample)

Jane Doe

Jack Doe

John Doe

Desire goals of the report:

1. Daily log as employees work e.g if Jane Doe works on 10/8/2013 as a Lead and Assistant, ideally the data entry person should be able to enter these two values on one cell or pick the relevant value from a set list

2. The report would then match the value of the data in the cell for Jane Doe on 10/8/2013 with a table array with categories in one column and the pay rate in another, and return the appropriate pay value which can either be a sum of the two rates or the total hard coded into the cell.

3.Be able to create a dashboard report that would sum the totals by employee, month,sessions etc in a separate sheet vs other criteria such a budget etc.

View 1 Replies
View Related
Oct 14, 2009

I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).

Range I4:I28 to the monthly sales worksheet by date.

View 10 Replies
View Related
Feb 21, 2013

Is there a way to conver a persons time spent (given in weeks) to adjust/convert to show per month. Attached is the sheet. Do note that week 2/25 - 3/1 is a combination of Jan and Feb so hours should be logically divided into jan and feb...

Name 2/18 - 2/22 2/25 - 3/1 3/4 - 3/8 3/11 - 3/15 Feb mar

Tom 40 10 0 20 ?? ??

name

2/18-2/22

2/25 - 3/1

3/4 - 3/8

3/11-3/15

Feb

Mar

tom

40

10

0

20

??

??

View 3 Replies
View Related
Feb 13, 2014

calculate overtime hours from daily time entries.

Normal hours are 7.6 per day

Time 1/2 is hours over 7.6 but no more than 2 hours

Double Time is all hours over that.

I have the spreadsheet with the days of the week in one row and at the end I have 1 cell for Normal Hours, Time 1/2 and Double Time. I need a formula that will work out overtime off each day and add for all days of the week and enter data into one cell. So all normal hours are in Normal hours and Time 1/2 and Double time are automatically calculated once hours are put in per day manually.

WedThurFriSatSunMonTuesTotal Normal HoursTime 1/2Double Time 10101068

View 5 Replies
View Related
Sep 25, 2013

I'm trying to create a time sheet to calculate how many hours worked in a week, Once it reaches 40 hours, The excess over 40 hours goes into a "overtime" cell. The "40" hours remain in the regular hours cell.

Attachment 267704

View 14 Replies
View Related
Aug 4, 2013

My overtime pays is anything exceed over 8 hours per day or over 40 hours per week. Right now I can only calculate overtime by either over 8 hr/day or over 40 hr/ week. I need a way to combine both.

View 9 Replies
View Related
Apr 9, 2014

I have been a long time lurker of MrExcel, but have always been able to find the answer to my question within the forums. However, I am stumped. I am trying to create a pivot table that will show incremental goals by week, when I have a set goal for the month already. However, I don't want to do a straight average across all the weeks in a month. I would like to see it continually grow during the month. How can i mathmatically do that in Excel?

For example:

Jim had 191 sales leads in the last week of the month in December. Of those 191, he closed 29 of them, for a 15.2% close rate. I want to see him raise that by 6% by next December, so (6%/12 (# months) = .5%). He will need to increase his close rate to 15.7% by the end of January. So, for the month of January, we expect 955 sales leads (5*191) and 149.935 (15.7% of 955) closed. However, I don't want to say Week 1, 2, 3, 4, and 5 in January should all have 29.987 closes, I would like to be able to say week 1 = 25, week 2 = 27 week 3 = 30 week 4 = 32 and week 5 = 34, but have that incremental increase to total 149.935.

There is (sadly) no changing the percents, or the closed leads number. These have already been submitted and aproved. I just have to be able to let people see the goals by week now, but if they want to see the monthly goal, it must equal what has been approved.

View 2 Replies
View Related
Nov 24, 2009

I have a column where I am convering the Date into a Fiscal week number.

For example 10/6/2009 is Work week 41

Now I want to show October Week 41

I need to add the month and the text "Week" before the week number. what is the formula I use.

View 3 Replies
View Related
Sep 3, 2007

I have a list of employees that i need to simplify into groups as listed below

Employee No's

1-19

20-50

50-199

200-499

500+

In my list of data (attached) that i receive i currently manually count the employee numbers and then put them into the relevant bands dependant on the employee sizes. Example:

Employee List

4

28

35

46

503

376

2000

Employee No's

1-19 - 1

20-50 - 1

50-199 - 3

200-499 - 1

500+ - 2

View 11 Replies
View Related