Track Attendance Points For Rolling Year
Nov 8, 2013
I've started with an Attendance tracking template that I found in Excel. My company has implemented an attendance policy where each employee is allowed up to 10 points before they are terminated. A 1/2 point is given for arriving late or leaving early and a whole point is given for an unscheduled absence. The points stay on the employee's record for one year, after that time period it drops off their record. We want to monitor each employee's cumulative points.
So, I'm looking for a way to look back 1 year from the current date and add up the cumulative points over that period.
View 3 Replies
ADVERTISEMENT
Jan 11, 2009
I have a 4-level program. The levels are Club, Bronze, Silver and Gold. Each is awarded when a certain amount of points are gathered.
Club is 0 to 19 points and yields an 11% discount.
Bronze is 20 to 44 points and yields an 12% discount.
Silver is 45 to 59 points and yields an 11% discount.
Gold is 60 and better and yields an 11% discount.
I am looking for a way to total the points and have the spreadsheet automatically calculate the leve and percentage. So if my total points is 22, I would need the formula to return "Bronze" in one cell, then "11%" in the next.
View 3 Replies
View Related
Sep 22, 2009
What it is, I have a rolling report for the whole year, each with a score for the week.
However, it needs to show the whole year, but I need a seperate box with a summary of only the last 4 weeks on a rolling basis, ideally this would automatically update depending on the week.
View 9 Replies
View Related
Mar 28, 2007
My employer uses a scoring system to give a value to an individual’s sick record in the current rolling year.
Each person, has recorded, the number of sickness occasions and the total number of days in this rolling year period, these details go onto the final sheet where a score is calculated.
At present to get an accurate period, I have needed manually to deduct days to produce the correct time frame, ie 27/3/06 to 28/3/07 or when the sikness carrys on into the folling month.
I have tried without success to modify the rolling year formula, which as it is, will include the whole of March 06.
The formula is
=SUMIF(B5:D49,">="& DATE(YEAR(TODAY()),MONTH(TODAY())-12,1),L5:L49)
View 9 Replies
View Related
Aug 28, 2012
I have just been asked to update a file on a quarterly basis which contains a large number of charts based on the performance of various funds against their respective benchmarks. In column A I have dates going back to 2008 and then the fund and benchmark figures in the next columns. Each quarter there will be a need to update the charts to the new rolling 3 year period, i.e. in October when I update the file, the charts will need to show the results from 30.09.09 to 31.09.12
As there are 20 charts this can be quite time consuming? The charts sit on the same tab as the data, under the columns at the bottom.
View 2 Replies
View Related
Feb 25, 2010
I have a spreadsheet that tracks “points” for hourly associates on a daily basis that totals weekly and monthly. This spreadsheet works on a rolling calendar year instead of a fiscal and I need to be able to capture the totals for periods of time by days.
For example, I would need to view how many points ‘employee x’ has from 2/25/09 through 2/25/10. I have attached the spreadsheet, which includes tabs for each week ending and a summary page.
View 4 Replies
View Related
May 2, 2007
Is there a way of amending the following line of code so that it selects multiple data points in a data series in a chart (e.g points 14,15,16)?
ActiveChart.SeriesCollection(2).Points(14).Select
View 2 Replies
View Related
Oct 31, 2008
I got struck in preparing absenteesm report from 22nd Sep 08 to 21st Oct 08. I've to put the dates on which a employee was absent ...
View 9 Replies
View Related
Oct 4, 2009
in range B9:B187 I have names and I have their attendance in I9:AM187
I have all the dates in I8:AM8
attendance codes are PL SL CL LWP TRG & P. I want to segregate all PL SL CL LWP and TRG
I want the output in a different sheet where in column A I will should get name, in column B I should get type "SL PL CL LWP TRG", in column C I should get start date and in column I should get end date.
View 10 Replies
View Related
May 30, 2013
I have a list of names and want to print an attendance certificate off for each individual on that list. is there a way of doing this automatically or is it copy/paste.. print.. then do next one.
View 5 Replies
View Related
Feb 11, 2013
I am recording attendance to our various activities as follows (screenshot):
Column A show the activity
Columns B onwards show attendance by day of the week (names gathered from list).
Now I've got a simple formula =COUNTA(Sep!B4:AE51) to count the total attendance for the month, modifying the range to cater for each activity, but it would be better if I could keep the range the same and modify the activity.
Also, I would like a formula to show the attendance at each activity by the day of the week (ie to show the total and average attendances for Mondays or Tuesday etc).
View 5 Replies
View Related
Aug 15, 2006
If you take a look at the spreadsheet you'll see that there are 11 sessions. I'd like to figure out a way by looking at the attendance log to get the percentage/number of people from a certain unit that attended each session. The Y's means that they were present and the N's means that they weren't.
I tried = countif( range,"12WT") which I guess is wrong because it only counts the number of times the word 12WT appears. It doesn't count the number of time a person from 12WT attended the session which is what I'm looking for. How do I go about getting the percentage of people that attended from each unit?
For all the units that didn't attend, is there a formula that would list the units that didn't attend according to the session number? For instance for session one, someone for EC, 9WT, didn't attend. Is there a way that the end result for the formula could be: EC,9WT. Or, does the result for every formula always have to be numeric?
View 4 Replies
View Related
Jul 29, 2014
I'm making an attendance list that calculates regular hour and overtime hours for days in a month on rows. Besides the number of hours, there's input for v and s, which stand for vacations and sick days. I'm using the "if" function to separate hour and overtime, and when I put v or s in a cell it messes up my totals. Is there a way to make it so that the cells with v or s don't affect my other columns?
View 4 Replies
View Related
Jul 31, 2014
I am creating a spreadsheet that tracks daily employee attendance.
The title for the E Column is, "Received On Time?"
In the next column, I want to enter a formula that does the below:
If I enter Y, the cell reads "N/A"
If I enter N, the cell reads "ENTER TIME"
If the cell is BLANK, the cell should read "NOT RECEIVED"
So far, I have entered the below function in a cell in my excel spreadsheet, which worked perfectly:
=IF(E3="Y","N/A")
I also want to add in this same cell:
=IF(E3="N","ENTER TIME") AND =IF(E3="BLANK","NOT RECEIVED")
View 2 Replies
View Related
Sep 2, 2010
I would like to have a workbook with class lists for 21 classrooms.
Then I would like to use this each month to generate a workbook that has one sheet per class with the teacher name and class name as an overall header. The row stubs would be the student names. The 2 column headers would be the weekdays (Mon through Fri) and the day of the month as a number.
It seems that this could very well be an application that already exists. It seems like the kind of thing a business would create and put on the web as a free download as a good will thing.
Does such an application already exist? If not, is there a small example of populating a workbook with information in another workbook?
View 8 Replies
View Related
Mar 26, 2013
I am trying to create a 'simple' spreadsheet which will allow me to check how many children are going to be attending my nursery and split them into the different age bands.The columns I am using at the moment are
Name
D.o.B
Start date
Mon AM
Mon PM
Tues AM
Tues PM
I need to be able to split this data into three age groups: Babies (under 2 years), Tweenies (2-3 years) and Pre-school (3+).I would also like the formula to take account of the start date so to remove children from the list who will not have started by this data.
The idea is that I can use this as a quick look to see if I can accept another child / give me an idea of staffing needs. I know there is software out there that can do all this and more, and used to use them on previous nurseries, however this is a new start nursery which does not have the cash to pay for the software at the moment so I am looking to save myself a few hours of checking it all myself until the nursery is up and running and can afford the software which we would hopefully buy in year 2.
View 3 Replies
View Related
Feb 18, 2008
I would like for a vba programmer to tell me if the following program can be written before I attempt to code it. When I open Excel I want to see cells containing names in a square array with red backgrounds. As I click on each cell I want the cell to turn green and I want an alphabetized column of the names to have a 1 recorded(after I click) indicating that the person is present. If a person's name is red then I need a 0 recorded. If possible, I would like to be able move over one column each day that I take attendance by clicking on a cell in a row above the column of names. I am a math professor and I write computer code most every day but I have only written some small routines in vba that did not involve events.
View 3 Replies
View Related
Dec 6, 2013
I have been creating a simple database to hold a record of attendance.
I'm now at the stage where I want to create a worksheet that cannot be modified; but rather is something the user can look at in a glance.
I need forming a SUMIFS formula that will look for monthly periods of the times late, sick, absent etc and add them up to return to one place.
So the dependants will be the month, the employee and the type of record (late, sick, holiday)
I have created a userform that I can use to add in the raw data, so I'm not sure what range I can create, as every time new data is added it finds the next available row.
Here is what I'm using so far: [Code] ........
What the Overview will look like : Overview.PNG
What the raw data looks like : Raw Data to use.PNG
Do I need to introduce a matched up column that has the employee name and date?
View 1 Replies
View Related
Jan 13, 2014
I've created a spreadsheet it is for tracking attendance etc broken down into percentages.
What I'm looking to do is if someone doesn't attend this doesn't get added to the attendance total.
I've attached a screen shot so you can see what I mean.
excel.PNGā€ˇ
View 7 Replies
View Related
Jun 2, 2014
I have an attendance sheet for our Company Employees.
This attendance sheet shows the salary of the month from the total basic salary / hour and overtime / hour of each
employee.
I found a problem in this sheet. When i reduced overtime charges in the last of this sheet so the Total Salary of the month
was not changing. I couldn't found any mistake in formulas.
View 1 Replies
View Related
Sep 10, 2013
I've got an Excel 2007 spreadsheet that I want to use to take the attendance in the school where I teach. I've got it more or less the way I want it, but there are 2 improvements I'd like to make:
1. I don't want to have press Enter to go down to the next cell. If a kid is present, I want to press 'p' and automatically be one cell lower.
2. When I open the spreadsheet (which contains multiple worksheets for different classes), I'd like it to open at today's date on the left regardless of which worksheet I go to (I've frozen the first four columns, so I'd like 'today's column' to appear next to them). I've put the dates in row 1. I've put the dates in the format ddd, d/m/y. (I don't know if that info makes any difference, hence I'm including it). I've already looked up some posts on this and copied and pasted a few different suggestions, but none worked.
View 14 Replies
View Related
Sep 25, 2006
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here
View 3 Replies
View Related
Feb 6, 2013
I have an attendance file w/least 30 sheets. 29 of the sheets are for each separate group. The other sheet is the main one where I need to collect information like "total participants in attendance" and "total members in group" Then I divide those two and get the percentage of attendance.
The sheets containing the individual groups info are set up like this... (1=they were in attendance)
GROUP 1
Name / January / February / March/
Jess________1________0________1
Ryan_______1________1________0
Joe ________1________0________0
----------------------------------
total P ____3________1_________1
members___3________3_________3
% _______100_______33________33
The main sheet looks like this
Group / Jan. Participation / Jan. Member total/
Grp 1 ________3_____________3
Grp 2 ________8_____________10
Grp 3 ________7_____________10
---------------------------------------------
_____________18____________23
% total-__________________78.2%
I know I can manually go through and link the sums of participation and total group size into the main sheet, but I have a lot of workgroups and need to do this every month, is there an easier way? I am willing to change the set up of the sheets.
View 2 Replies
View Related
Dec 8, 2013
Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.
Month 2012 2013
Jan 300 100
Feb 275 100
Mar 400 150
April 650
May 454
June 800
July 500
Aug 375
Sep 525
Oct. 300
Nov 410
Dec 510
Sample file is attached : Comparison_Years.2011.xlsā€ˇ
View 4 Replies
View Related
Mar 8, 2014
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsxā€ˇ
View 4 Replies
View Related
May 14, 2009
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
View 3 Replies
View Related
Dec 13, 2011
I am trying to do an IF statement, if the activecell's value is equal to this current year, do nothing and if its not the current year, to offset one column to the right and insert a blank column, then copy the whole column to the left and replace it with the new blank column.
View 1 Replies
View Related
Oct 13, 2008
i have the following table of information
Year DOB
7 01.09.96 -31.08.97
8 01.09.95 -31.08.96
9 01.09.94 -31.08.95
10 01.09.93 -31.08.94
11 01.09.92 -31.08.93
and a list of dates i need to look up the date to see which year it falls in and return the year
View 9 Replies
View Related
Feb 27, 2009
DATE function won't return TODAY()'s year in the "year" slot.
Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?
View 9 Replies
View Related
Sep 29, 2006
I know that in order to draw a chart where a data line for a certain period is compared with the same period the previous year, one should have the 2 sets of data of different year side by side columnwise. However, is there a way where I could still churn out the same line chart when the data is all on a single column?
View 9 Replies
View Related
