Student Attendance Record

I teach college English, and I have started to use Excel calendars for attendance. I like to have all the attendance for a class on one page for quick reference. Templates I have downloaded, though, include all the days in each week. How can I set up--quickly--a calendar containing only Mondays and Wednesdays or only Tuesdays and Thursdays?

Also, I want an automatic record of each student's attendance tallied for grading purposes. Students are allowed two absences, after which I subtract one point from their averages. Three tardies add up to one absence. I would like to import the result to the student grade sheet, showing the number of absences to be deducted from the student's average. I have worked out a way to accomplish this, but it isn't exactly a time saver. Any ideas?

P = present
A = absent
T = tardy or .33 P

Craig

First questions - probably with autofilter. Especially if the layout is vertical. After showing only "non-teaching" days you can mark all visible rows with dates and delete them (if dates are a result of formula do copy-pastespecial as values before filtering).
As for grading usefull functions could be COUNTIF or COUNTIFS and probably sooner or later you will use also VLOOKUP.

Not seeing attachement it is probably end of suggestions.

141 ENGL MW Spring 2014 Attendance Template.xlsx

Originally Posted by Kaper

First questions - probably with autofilter. Especially if the layout is vertical. After showing only "non-teaching" days you can mark all visible rows with dates and delete them (if dates are a result of formula do copy-pastespecial as values before filtering).
As for grading usefull functions could be COUNTIF or COUNTIFS and probably sooner or later you will use also VLOOKUP.

Not seeing attachement it is probably end of suggestions.

I tinkered with it this morning and I think I have what I need for now. (Next step is to figure out this forum's posting system ;-)
Not sure the attachment I posted is here or not.

So I have classes that meet 31 times this term. I entered class dates manually because the classes meet only twice per week, so there was no need to include all the days of the week. There's probably an elegant way to accomplish this, but I'm a novice with Excel, self-taught so far.

Total column is the total number of class meetings (31, entered manually).

For each day I give the student 1 point (present), 0 points (absent) or .66 points (tardy). The Points column is simply the total number of classes minus the absences minus the two allowed absences to get the number of points I need to deduct from the student's average.

The real trick is getting the data out of our university's Banner record keeping program into a form I can use in Excel. To do that I select all the class record data and paste it into a temporary spreadsheet so I can then extract columns of data, which I can't do with Banner.

Anyway, this spreadsheet will work, think. Thanks for your response, and of course I would welcome any comments or suggestions.

Craig

I am not sure if you plan to write these 1, 0 or 0.66 manually or import it from the system.
If the form is to be filled I'd rather write letters P A and T in columns H and next
then the formula in E4 could be:

Please Login or Register  to view this content.

and in F4:

Please Login or Register  to view this content.

(or - may be - tardy here not in the first formula, so:

Please Login or Register  to view this content.

then you can copy E4:F4 down

May be the formula for G4 could be:

Please Login or Register  to view this content.

In the attachment I also used conditional formatting in column F to automatically highlight more than 2 (allowed) absences.

Treat all above just as propositions - I do not say all are appropriate or usefull for you.

Try this..

1) Pivot table to get the dates
2) CountIf formula to track grade % deduction