Find Courses That Employee Has Not Taken From A List?
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
ADVERTISEMENT
Jun 19, 2014
got a formula that checked C4, then D4 for dates (either in or our of date) and returns a count of 0 or 1, with some cells having tet in them also.
The formula I am currently using is below
=IF( OR(F4 ="enrolled",F4 = "O/S"),0, IF(AND(G4="",TODAY()-F4>730),0,IF(AND(TODAY()-F4>730,TODAY()<G4),0,1)) )
I have a range of four courses (all child protection) that should be attended, some are eleanring, some local authority courses and staff should attend at least one but up to all four of them. Although they all fall under the question are they compliant or not (i.e.0 or 1)i do not want to count them all seperately. Is there any way I can look at all eight cells related to these courses and assess the latest date to assess if they are complinat (in date) or non compliant (out of date)?
I have attached the spreadsheet which I hope will clarify things
=IF(AND(E4="",TODAY()-D4>730),0,IF(AND(TODAY()-D4>730,TODAY()<E4),0,1))
View 8 Replies
View Related
Oct 5, 2012
I have been given a task to compare a set of the current months employee records to last months employee records. The goal is to a) find employees who are still in our group but may have changed managers b) find employees who are no longer in our group and c) find employees that are new to our group. Once this information has been compared between the two workbooks, it needs to be exported to a new spreadsheet that needs additional columns added for formatting in order to import the new spreadsheet in a tracking tool.
For example, last months spreadsheet includes the tracking tool ID column and has a row for each employee. For an employee that is currently with the team they would need the tracking tool ID to be put in the newly generated spreadsheet. Additionally the current employee might have changed managers and that needs to be updated. A new column needs to be added to the new spreadsheet that shows that a current employee is "Active". For employees that are new to the team they won't have a tracking tool ID because they are new but would still need to be considered "Active". And for the employees that have left the group, they would also need the tracking tool ID from the old employee record but would need a column that would mark them "InActive" since they are no longer with the team. My last step is that I want to add a column called "Team" that would iterate through each employees managers and assign them to a specific team based on the manager that they have.
My general idea has been that I need to loop through all of the employee names or numbers from this months records for comparisons to the names of employees from last months records. Once a match has been found I need to copy the entire row from the current months to the new spreadsheet. That way I will get the latest info or "row" for a current employee and that would also handle finding if they have a new manager. Next I need to pull over the tracking ID from the last months records, create a new column called "Status" and make the employee "Active" and create a "Team" column showing the team.
For employees that are no longer on team, I need to copy the whole row from the latest employee records, pull over the tracking ID from the last months records, update the "Status" column and make the employee "InActive" and populate a "Team" cell showing the team. And lastly for the new employees, I need to copy over the row from this current months spreadsheet. They wont have a tracking ID (It will populated when the Excel file is imported in the tracking tool) and I need to update the Status column to "Active" and also provide their "Team". After the new spreadsheet is generated I am done. The file can be imported. And the next month I need to kick off this script again.
View 1 Replies
View Related
Nov 3, 2011
I am trying to automatically generate an organisation chart in excel from a dynamic list of employees. in column 'A' will be the "manager's name", column 'B" will be the "employee name", column 'C' will be the "employee number" & column 'D' will be the "employee days" number (an potentially a few more employee attributes)
I would like a printable org chart that is created on another tab which is generated from this list, ideally with the "employee name" and their other attributes shown in the chart.
how this can be done in excel, maybe VBA?
View 3 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
Nov 26, 2009
First.
I am working on a costing sheet for work and I need a formula to add in employee wages.
Here is what I am trying to do.
Cell 1A - I have a drop down list of employee names
Cell 1B is where the number of hours an employee worked on the job will be entered
Cell 1C is where the employees wages will come up.
Cell 1D will be 1B*1C
What I am looking for is the formula for 1C where the wage will automatically come up when their name is selected in 1A. I guess this would be a IF command?!?!
Second question.
In order to have a drop down list (the instructions that I found anyhow) requires the list in the dropdown to be somewhere on the same sheet.
Can this be circumvented to retrieve the list from a different spreadsheet altogether? Or from a different sheet within the workbook?
View 5 Replies
View Related
Feb 6, 2014
I am looking for a formula to search two columns.
1) The first column needs to be search to match a text string.
2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.
So:
Joe 50
Jim 12
Joe 10
Rob 25
Jim 8
Rob 99
When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".
View 4 Replies
View Related
Jul 31, 2008
I have a list of 200,000 e-mail addresses in a excel document in column A. What i am trying todo is find all the duplicate e-mail addresses within the list that appears more than four times.
I have tried this forumla in colunm B so it lists how many times the record appears: =countif($A$2:$A$244270,A2) which works fine and then a number appears next to each record. Then i pressed the sort filter to arrange all the records in number order so all the 5's are at the top of the worksheet but that does'nt always list them in the correct number order.
Basically all i want todo is find all the records that appear in my list more than five times then make a list of just one of those 5 records so i can remove them from my e-mail system.
View 9 Replies
View Related
Sep 28, 2013
Easy way to find the drop down list , if more than 500 list. at least the very first letter of the text.
View 1 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
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
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
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
Jul 5, 2014
I modified an employee schedule that I found on here to meet my needs but I am needing one more thing:
I need a field (or separate worksheet in the workbook) where I can enter employee availability and if they are unavailable for a specific day, it will auto update in the calendar stating they are unavailable for that day.
View 7 Replies
View Related
Nov 19, 2008
Any non-commercial roster template that would suit 7 day/24 hour/4 person shifts?
Currently we use a totally manual spreadsheet which looks like a Rubik's Cube and does not offer any form of automation in order to speed up entry adn ensure no doubling-up or missing shifts.
View 9 Replies
View Related
Oct 6, 2009
I would like to create a formula that would verify that specific work shifts have been covered each day. The spreadsheet has already been created by someone else, so I am hoping not to recreate the wheel.
The goal is to make sure that all desired shifts are covered with a result in the last cell of the column that would indicate "covered", "not covered" or even a true or false statement.
As an example, an 8am shift is needed. The choices are 8A or 8ALEAD and only one of these is needed for each day.
Can something be set up with conditional formatting from a master list of required shifts.
such as: 7OR, 730*(for just 730 and 730LEAD), 11A, etc.
This is just a snippet of what the schedule looks like: ...
View 14 Replies
View Related
May 4, 2013
I'm trying to come up with an Employee Shift Scheduling template. I have 6 employees total with 3 different shifts (day, swing, graveyard). I would like to not do a rotating shift. I'm trying to figure out if a 5X8 shift is better or 4X10 and I would like to come up with a weekly schedule where I can see what shift everyone is working and were I have double coverage.
View 1 Replies
View Related
Jul 7, 2009
how can I sum up all the "Type of Defect"[CR, LOF, DR] for a specific Welder. They were tag as Welder 1-7. In the attached example (Sheet1- Data), Welder 1 has 2 "CR" and 1 "LOF", this figures to be reflected in Sheet 2 (TOD).
View 2 Replies
View Related
