Staff Holiday Analysis?
Dec 17, 2012
My boss wants me to take the holiday info from SAGE for 80 employees and create a record on excel. He wants to know what holidays each employee has taken and is due to take throughout the year. As each employee works a different amount of hours and a different shift pattern, SAGE records their holiday entitlement in hours rather than days. I have attached an example of one employees details and if come up with an excel document containing similar information for 80 individuals - all starting on different dates and all having a different amount of holiday entitlement. He wants to be able to look at each employees record for the year and see not only holiday data but sick days too. I don't know where to start with this - I've thought about creating a workbook with 80 pages and create a 12 month calendar for each individual with days off marked on it?
View 4 Replies
ADVERTISEMENT
Sep 17, 2013
Currently I'm working on functionality in my staff holiday spreadsheet and I've come up with this amazing idea for a calendar overview for month by month.Now the sheets I currently have is summary and jan to dec.
I plan on adding the sheets Jan (Cal) to Dec (Cal) now this is when my idea gets nifty - The calendar view will import the information from the month table and will show it on the calendar.
For example X has 1 holiday in July for the 20th - this will show up on the calendar as "x on holiday", I'm still working with spreadsheets so I'm not sure if excel is powerful enough to do this - but is this actually possible?
View 2 Replies
View Related
Nov 22, 2006
in excel, is there a place called data analysis under tools? im an exchange student in sweden andthe work that teacher gave me is half swedish and half english.. im looking for histogram there.
View 3 Replies
View Related
Nov 24, 2013
Is there a way I can count the number of different people in a column in any one day. The same persons name may appear more than once on the same day. My table is as follows:
Column A has my list of dates running from A2 to A500
Column E has my list of workers running from A2 to A500
My table of results is in a different workbook but basically what I need is this:
Nov 1 - Count how many people worked on 1st November
Nov 2 - As above
Nov 3 - As Above
Nov 4 - As Above
Nov 5 - As Above
etc
View 6 Replies
View Related
Jul 28, 2014
I need counting the number of staff within a time period. Unfortunately the schedule structure does not allow application of the simple solutions I've found regarding this which results in this being more complicated. My example is attached. My start and end time are in the same cell. I have used the Left() and Right() functions to manipulate the times. I am unable to count the number of occurrences of between the start/stop times of all my employees in the array. The formula also needs to take into account a manually entered time on the actual day.
Basically:
00:00 if F9:F42 = time is TRUE and I9:I42 = "" then count occurrences of 00:00 in scheduled times F9:F42 + if F9:F42 = time is FALSE, then if I9:I42 = time is TRUE then count occurrences of 00:00 in scheduled times I9:I42
01:00
02:00
"
"
Even when I think I've gotten close the midnight time crossover keeps throwing off.
Schd_05_TEST.xlsx
View 3 Replies
View Related
Feb 12, 2012
I have a table on a sheet called Contracted Staff. Table has staff names down the side and along the top is training needed.
Training H & S. fire. General training
Lucy. 5/4/12
Jeff. 5/4/12
Michael.
Dawn. 5/4/12
I would like on a training summary sheet to tell me who hasn't completed the training. I know I can use auto filter to select blanks to get who hasn't done it. However I want to print every training and list on the same sheet.
View 2 Replies
View Related
Nov 16, 2008
I need a formula to compute holiday pay. I am having problems when there are two holidays during the month and the hire date of the employee is after the first holiday. For instance in November, November 11 and November 26 are holidays. If the employee is hired before November 11, he gets paid for the two holidays. If he is hired after November 11, he only gets the second holiday.
A B C 1 HOLIDAYS 2 HIRE DATE RATE/ 11-Nov-09 3 HOUR 26-Nov-09 4 5 10/15/2009 60.00 6 11/16/2009 60.00 7 11/28/2009 60.00 8
View 9 Replies
View Related
Feb 25, 2014
From the attached data is it possible to create a formula to count the number of staff in post that are line managers?
View 7 Replies
View Related
Nov 6, 2008
I am trying to work out a % score for a telephone operator. To explain further, I would monitor a call that an agent takes. The agent starts at 100%. There are 20 points to be scored and they are scored like this
1 - Yes
0 - No
left blank - n/a
B3 counts the number of entries in the column
B2 counts the number of 1's in the column
B28 = B2/B3
Seems to work fine until the agent makes a fail which would enter a 0 in the column. Somewhere I think I need to take account that there are 20 possible fails or passes?
View 6 Replies
View Related
Nov 13, 2013
How to get the card# from staff ID as lookup value? ask.PNG excel question.xlsx
View 6 Replies
View Related
Apr 9, 2014
I have a table that will have a drop down list in all of the cells in column F that are in the table. (I think I have the drop down list figured out
I want to create a button that makes it so every time a new row is needed for new stock entries etc, the user just has to press the button and it should just create a new row within the table. Simples .
Is this achievable? And if it is, what is the best way to go about it?
Table width is from Column C - Column J.
View 1 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
Jun 10, 2014
I have 20 staff members that I need to schedule in half day shifts (AM & PM) across three different locations for the month of July.
I want to make sure there is no overlap/duplication for any one staff in a time slot. Nor do I want any one staff member to work an AM and PM shift in the same day.
Ideally, I would also like to avoid back-to-back shifts (e.g., not allow PM on Monday to be scheduled for an AM on Tuesday).
Lastly, I would like to have the number of shifts be be as equitably distributed across all of the staff members as well.
Any way that I can do this in Excel?
View 1 Replies
View Related
Jan 17, 2007
I have a problem with a cell displaying the correct number of days.
We work a 38 hour week and therefore each day is 7.6hrs
I have a start date and current date and calculate the number of days employed. I know that each day employed is worth 24 minutes, so over the year it calculates to 20 days per annum.
as I have not entered all the hours taken off yet, my current holiday entitlement is 767:04 (hh:mm).
when I process this on the calculator it tells me I have nearly 101 days holiday due, but I can not get excel to display XX:XX:XX (DD:HH:MM)
What would like to know is how many days that equates to using the 7.6 hour days
View 9 Replies
View Related
Jul 5, 2014
I have this calendar with a total of 31 staff assigned. I need to assign two staff to cover each day not grayed out utilizing the staff tab from the top (A1) to the last (A31). Need to go to next name if blank.
Example:
Bob
Bill
James
Kathy
Should show
Bob
Bill
then
James
Kathy
not
Bob
Space
Bill
James
View 3 Replies
View Related
Aug 4, 2014
I want a formula to calculate the average number of staff working per week in the attached data.
View 9 Replies
View Related
Oct 22, 2007
I have a sheet set up to record free pour tests for my bar team.
Column A has the date.
Alternating columns from B (B..D..F.. etc) hold a drop down with the staff names
Alternating columns from C (C..E..G.. etc) hold a drop down with either pass or fail as the result.
What I need to do is count the number of times a particular staff name appears, but more importantly how many times they pass or fail.
I can easily count the names, but how do I count if they have pass or failed?
View 12 Replies
View Related
Aug 31, 2012
I'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.
Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B$3,David!$A:$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.
The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.
I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.
View 7 Replies
View Related
Feb 25, 2013
I have data showing when staff arrive at, enter after clearing security and then exit a building after completing their task. The data for each cell is a combined date and time as opposed to separate cells for date and time.
I am trying to ascertain how long our security staff take to clear persons waiting to enter the building and also how many people at any one time are actually in the building (after being processed through security).
I also need to ascertain if particular issues arise at certain days of the week (eg Mon morning) and so need to add a further column which shows the day of week using the date/time data.
A sample of the data is as follows;
Staff No:
Arrival time
Processed time
Exit time
1
26/02/2013 07:13
26/02/2013 07:22
26/02/2013 13:52
[Code] ........
I had thought about trying to display the data using a a stacked bar chart but I still need to look at any particular date/time and ascertain how many persons are in the building without manually counting the rows!
View 6 Replies
View Related
Nov 4, 2007
At my job, employees accrue X hours vacation per 2 weeks worked. This makes it difficult for everyone to plan vacation (IE - will I have enough vacation in January to go on vacation for 2 weeks?) I'm trying to create a calendar in my spare time to help out, but it's turning out to be quite a bigger task than I've ever attempted in excel. Therefore, I'm coming here hoping someone will point me in the right direction.
I found this:
[url]
It does the majority of what I need. However, as it costs money, I'm afraid they've locked down editing of things and I won't be able to add functionality to it. It'd be annoying paying them and then finding out I can't add vacation days earned, as the spreadsheet is locked.
Features I'd like added to the above program:
- Add another checkbox (or similar) to show hours of vacation accrued, as well as an original date and hours to start counting from.
- Highlight days corresponding to what kind of day it is. Weekends = red, holidays = green, vacation = yellow, etc.
What I started with:
[url]
This gives me a calendar with the dates in the right place. I've created macros to identify holidays dates. It's starting to get pretty annoying using conditional formatting on everything to highlight days. Especially since I have to do it for each of the 12 months, as it has a hard-coded month cell in there.
View 9 Replies
View Related
May 2, 2006
I found a link to a website on one of the forum pages. I had a look in the website and it showed a formula for calculating when Easter falls - I didn't know it could be worked out, but it can!!
I therefore decided to investigate further. I picked up another formula to calculate the first MOnday in May and I have now put together a little spreadsheet that will calculate all bank holidays in the year entered in cell B1. It also takes into account additional bank holidays that exist when Christmas Day and/or Boxing Day fall on a weekend.
View 3 Replies
View Related
Aug 19, 2007
where I might find some?
View 3 Replies
View Related
Oct 2, 2012
I have a spreadsheet whereby process activities can be attributed to the different staff members who complete the activity. Each activity is on a different row and up to three staff can be assigned to each activity, so for example if a level one employee sorts the mail on four out of five days per week I would attribute 80% contribution to the task to the level one employee. If a supervisor sorts the mail on a Friday I would allocated a 20% contribution to that staff group.
My question is how do I prevent someone filling in the spreadsheet from allocating a combined contribution above 100% to any task. This could be for example either putting a number in greater than 100% into one of the allocation columns or a total across up to three different columns above 100%.
The columns where I assign the percentage contribution are E,H and K. I would like to generate an error which says something like 'Cumulative staff contribution per process activity cannot exceed 100%.
View 6 Replies
View Related
Jan 21, 2010
using IF formula [ =IF(SUM(C3),LOOKUP(DATEDIF($C3,NOW(),"y"),(0,3,),(0,3,)),"")] holiday sheet after 5 years service employee get 3 days extra holiday trying to in E3 to add 3 days to holiday entitlement to take from 16 days to 19 days
View 2 Replies
View Related
Oct 10, 2008
i have this holiday program (this is linked to networked excel spreadsheets in the origianl) and what i want to do is create a macro that will create an appointment in microsoft outlook calendar for each date everytime i select the cells and click the button with the persons name in the subject. is this possible?
View 13 Replies
View Related
Jul 8, 2014
Gantt chart to calculate holidays, it now only calculation weekends only, file can be downloaded from here.
View 2 Replies
View Related
May 21, 2009
I am trying to work out a formula calculating dates.
basically a piece of work / report is due for completion twenty working days from a trigger date. And I need the formula to calculate this due date. Im using the workday function, which calculates a date using the number of working days given after the trigger date.
this would work fine, except that the trigger date needs to be included in the calculation (inclusive). I have tried changing the value for the number of working days to nineteen, which would logically give the right answer (20 days including the trigger day). BUT the bloody thing doesnt work, because the trigger date can be on a weekend or holiday!! so then it calculates 19 days (as it should) but the result is wrong because the trigger day isnt included (as its a holiday/weekend).
View 9 Replies
View Related
Feb 17, 2006
I need to have all the information in one workbook for all 100 employees and still provide an individual report for each user and an overall report. In the attachment is a "Summary" and "Calendar" sheet these are the original individual files. summary contains personal information and the "calendar" tracks absence and holiday and the totals returned to the Summary sheet. I think what I need is a Master worksheet that has all the information in for each employee pre row and to have a "calendar" input/change/ call worksheet sheet to call an individual detail, this would also update the master worksheet with any changes.
View 4 Replies
View Related
May 29, 2008
I have one workbook with two worksheets (Jan-JUly and Aug-Dec). I am using a userform to add data into worksheet ("Jan-July"). But I do not have any clue - How do I update the other worksheet (same data like name,join year etc)worksheet("Aug-Dec") at the same time. I used two worksheets because worksheet doesn't support 370 columns, and to make my life easy. UserForm add data into Column A to M (worksheets Jan-July), rest is done manually. I have also attached the file.
Private Sub dataAddButton_Click()
Dim dataCheck, eMsg As String
Dim strLastRow As Integer
Dim x As Double
strLastRow = xlLastRow("Jan-July")
'get Last Row
With dataInput
'dataInput name of userform
If (.fNameBox.Value <> vbNullString And .fNameBox.Value <> vbNullString And .joinYearBox.Value <> vbNullString) Then
'textbox validation value can not be empty......................
View 2 Replies
View Related
Jun 30, 2014
I would like to calculate staff meal deductions for each employee based on the number of hours they worked for that day.
I have the hours listed all on one page, per day, per employee.
I have the staff meal deductions range listed on another page in the workbook.
The range is as follows: *Please note anyone working over 8.00 hours is deducted a maximum of $2.40 for that shift/day.
Hours
Deduction
1.00
$0.30
[Code].....
View 4 Replies
View Related
