Employee's Project Report
Jan 26, 2010
I have created a spreadsheet in Excel where it gives me a report of how many hours my employees do per week and it seperate them in different categories.
What I would like to do is find a way to match the cell's description with the amount of total hours that were spend on certain project.
So here is the scenario.
In my department there 6 employees that are assigned to work on certain projects in daily basis. These projects are called CRs and to identify them I've added a number after them.
So we will have CR0001, CR0002, ect. These are unique projects.More that one employee that could woork at the same project as well on other sections but I am only interested on the projects.
To help you understand what I am trying to do I will give you an example.
John is working on project CR0005, CR0006, CR0001. He has been working on them three projects for the last week.
Here is the summary of the hours:
- 5 hours on CR0005
- 10 hours on CR0006
- 5 hours on CR0001
This information is inputed in Excel spreadsheet Week 1 .....
View 12 Replies
ADVERTISEMENT
Aug 28, 2007
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
View 4 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
Jul 13, 2006
Need formula to calculate employee turnover ratio and if there is a place to get a free template to download?
View 9 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
Nov 24, 2013
Uses excel 2010 to track the project in the form of project status report. Basically it has to look simple yet tracking should be in the form of progress bar etc.. Our aim is to track a 3month project.
View 1 Replies
View Related
Dec 27, 2011
I recently showed my supervisor (again) how to double click on a pivot table to generate a worksheet that shows the records that were used to calculate the pivot table information. He's finally impressed and now wants me to perform the following miracle:
Workbook 1 contains multiple pivot tables showing completed project costs. The tables are broken down by various topics such as designer, worktype, project number, and amount of overrun or underrun. I update this report each month with completed projects.
Workbook 2 is a new report recently developed which contains contract modifications for ongoing and completed projects. It is updated weekly but may be updated monthly in the future.
He would like to click on a project number in Workbook 1 and have it generate a report with Data from Workbook 2 showing all the contract modifications that were written for the project. I told him this would probably work better in Access but he gave me "that look" because he does not want to use Access. This will be shared with other members of management who also do not want to use Access.
View 4 Replies
View Related
Aug 4, 2014
I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
View 5 Replies
View Related
Sep 5, 2013
My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook
[code]....
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Number
Project Description
...
1111E.000000001
[code]....
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
View 4 Replies
View Related
Jul 30, 2013
I am trying to get excel to open a MS Project file and then run a project macro on it.
What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.
For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder
EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END
[code].....
View 9 Replies
View Related
Apr 27, 2007
I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.
View 9 Replies
View Related
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Jul 2, 2008
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies
View Related
Apr 12, 2009
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies
View Related
Sep 29, 2009
This is what I am looking to do and I am having an issue with it.
worksheet "beeble"
Column B has all the employee numbers listed from B3 to B100
Column C needs to have the emp name put in to them based on the emp number.
worksheet "weeble"
This sheet has the list of employees with their Emp#
A2 down to A99 is the number B2 on down is the name that belongs to the emp #
At issue is sheet "beeble" changes day to day depending who is scheduled to work in a certian area, otherwise this would be quite easy.. It is very easy for us to put the emp# in instead of the emp-name, so that is why wwe would need to be able to pull this information from the other sheet.
This may end up being very easy, but it is beyond me, and I cannot find what I need from a book, as I spent last night on here searching and reading through a few of my books..
View 13 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
Sep 11, 2007
SEE ATTACHED FILE
All,
I have a spreadsheet that lists each employees hours for the last 4 pay periods...each is in it's own row I am trying to find out their average.
I am using the subtotal function to average their hours and that works fine. BUT...my boss doesn't want to see 4 rows for each person. He just wants to see one row for each person and when I collapse the rows, it only shows the employee's ID, not the name (because that's what I told the subtotal function to do...add subtotal after every change in employee ID).
name appears instead of the employee ID? That's useless to him because he hasn't memorized all the Employee's IDs.
View 13 Replies
View Related
Apr 17, 2008
I was given a task of calculating bonus for number of projects per employee. The maximum number of projects per employee is 30 and they have completed different number of projects. Data is as follows:
Column A - Name
Column B - Date
Column C - Project
Employee name repeats one row per project and project repeats as they are working with it.
I need to list individual employee names in column D and the number of projects each employee has done in column E. A project can be saved many times thus creating many rows for that same employee. Do you think it is possible to accomplish this. One formula for column D and one formula for column E. If needed I can attach an example file or take a screen shot of it.
View 9 Replies
View Related
Feb 2, 2009
i have two col A and B
In col A name of the employee
In col B there production for the Month
I need in c col to rank them that according to there production for the month like 1,2,3, etc.
View 9 Replies
View Related
Oct 27, 2007
how to use formula in the monthly contribution field to calculate 7% of pay (salary)
Lenght of Total pay Monthly contribution cummulative
Service retirement value
3.6 $528.94
Please one more thing. How do i use FV Function, monthly contribution, and the lenght of service field to to determine the current value of the employees retirement palan.
View 10 Replies
View Related
Nov 2, 2008
i am creating a weekly time sheet for my company.the problem that i have is when the persons time reaches 40 hours, the time needs to be calculated in the overtime field. this is really tough for me when the person reaches 40 hours in the middle of the work day. I cant figure it out. i have attached the spreadsheet if you would like to look.
View 2 Replies
View Related
Sep 16, 2008
I'm trying to create a weekly employee schedule using Excel 2007 and have several issues but will start with one at a time till we get them all resolved.
I put in my start hour '6' in cell 'B7' and get this: 1/5/1900 12:00:00 AM.
I would like for to display 6AM.
I figure if I get his cell fomatted that I can get the other times correct then go on to the next issue.
View 10 Replies
View Related
Apr 1, 2009
I am using excel as point of sale book (to record customer name, service, and total price etc.) at our hair salon. We have employees that may be there to manage alone from time to time, and enter clients into excel.
Our worry is straight forward, and involves them erasing what they wrote. I am confident that the actual service and price is entered at the beginning, but want to track their changes to their own entries.
The "track changes" would work if it "tracked changes" after entry. But it seems to track the last change from saving. For example....the employee enters $40.25 presses enter--after she knows she can get away with a change, she may erase it altogether or change it to say $16.75.
View 9 Replies
View Related
Apr 8, 2009
I have problem to make an excel evidence which will track total working hours of employee during the month, how many times employee was late for work and how many times employee was on lunch break longer than 30 minutes.
And I made table which count all these things but problem is Saturday because on that day start work is 9::00 AM and all other days is 8:00
Formula for counting how many times employee was late is:
COUNTIF(D8:AC8,">0.336805555555555555555555555555")
where D8:AC8 is range where are all start work times in month and 0.336805555555555555555555555555 is 8:00 AM (serial number which is equivalen of 8:00 AM)
How to improve this formula to count properly because now it counts as a late when employee start work on Saturday on 9:00 AM (and that shouldnt count as a late)
[IMG]file:///C:/DOCUME%7E1/Nesa/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG]
View 9 Replies
View Related
Sep 26, 2006
I have a scheduling problem that I'm trying to get my head around. Im sure that some kind person can give me some ideas on how to get this started. It may be extremely easy, but I just cant get it off the ground. Basically I have 15 doctors that work for me - 9 full time (38hrs/week) and 6 part time (15+hrs/week) in a general practice. We are open 7 days a week
Open Close
Monday8:3019:00
Tuesday8:3019:00
Wed8:3019:00
Thur8:3021:00
Fri8:3019:00
Sat9:0018:00
Sun9:0018:00
Doctors have 5 days on and then 2 days off.
The below is the proposed daily schedule and the doctor requirements. % of booked patients for the times and % of random patients for the times are included as well as average waiting times.
Booked Random Waiting Doctors
8:30 - 10:00Morning Hours55%45%30min5-7
10:00 - 13:00Increasing80%20%45min7-8
13:00 - 18:00Busy 100%01.5hr8+
18:00 - 19:00Decreasing70%30%1hr6-7
19:00 - 21:00Night 40%60%30min5+
There are 3 types of patient visits
ShortDoubleTriple
%0.810.140.05
Minutes153045
58% of patients choose their doctor and 42% come in off the street and will take the first available doctor.
and finally - doctors cant work for more than 4 hours without a break. brek details are below
HoursBreak
<4hrs0
4 - 5hrs10min
5 - 8hrs2*10 mins - 45min lunch
8+2 * 10min 2*45min lunch
I need to create a timetable for the doctors which optimises their time based on the above details. I also need to design a data sheet for reception which will maximise the number of patients seen. Finally I need to calculate what the maximum number of patients the centre can see on an average day say monday.
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
Dec 10, 2013
looking to find a formula to find the courses that an employee has not taken from a list. I have a list of courses in column F, in which I can compare to column C using their EmpID number from column A and obtain a result as taken or missing in column D. I am currently using :
[Code].....
or
[Code] ....
to get the results in column D. However, with these two formulas I am getting all the courses taken by each employee and not the missing courses.
View 8 Replies
View Related
Jan 24, 2014
I need a formula that will be able to show what shift someone is on if the shift changes, for example. if one of my employees shift changes from and 8hr to a 10 hr on january 16th 2014 then I would like the raw sheet to show the old shift, the new shift and when the new shift started (date). the index formula I have will only show one shift even if there are new dates with a different shift. the formula pulls from the sheet name editor.
i really want the shifts to be different if it was change on a certain date then from that date the employee shift will reflect new shift but still keep the previous shift prior to the day the new shift started.
I've attached a sample workbook to show.
MDT Report December 201322(FILEminimizer).xlsx
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
Feb 27, 2014
I have a Log In/Log Out report that I need pulling data from.
Ultimately what I want to do is get the amount of time my employees were logged out.
I am using Excel 2003. Attached is an example of the data I'm working with.
Sheet 1 contains the data, and Sheet 2 will be the summary.
So, looking at Bob's times: If column A contains Bob (A2 in this case), and the same row in column G (G2) contains 79, subtract the contents of the same row in column F (F2) from the next row down in column D (D3) (which would be D3 - F2). That result should populate on Sheet 2 next to Bob's name in h:mm:ss format.
If there is nothing in the next row down in column D, then do nothing.
I would like to leave the data as is, if possible. There are extension numbers next to the names in the data. If possible to ignore those when rolling up to the summary that would be useful. Also, on the summary sheet, if the employee is not on the data sheet (if they had the day off) they can have 0 or NA or something to indicate there is no data for them.
View 1 Replies
View Related
