Moving quarter attendance, please help?

[Guest]

How can I track attendance in ecel with a moving quarter? The following is an
example:
Employees will receive one (1) point for each full day absence without
regard to limit, and for any Leave of Absence (3 days or more) not covered
under FMLA. If an employee works less than one hour of their shift (unless
and extreme, verified medical problem or a verified emergency occurs), it
will be treated as a full day absence.

Employees will receive one half (1/2) point for each incident of late, leave
early, and leave early. and returns (Lesser incidents (4 minutes to 1 hour)
of late arrivals, leave early, leave early and return, and no punch or out
will be one quarter(l/4)point per incident) Also, a No Call(an employees
failure to daily notify the Company at least one (1) hour before the start of
their shift on the 24 hour Call-h Phone only, of the reason they are unable
to report for work), will be given and additional one-half( 1/2) point (**
Note: though still required to call at least one (1) hour before the start of
their shift the one-half (1/2) point will be assessed only If the call Is
made less than 30 minutes before the start of their shift).

The attendance program’s progressive disciplinary action is listed below in
total number of incidents per moving quarter (90 days) or sooner.
Thanks in advance!

 

[Guest]

I would set this up as a database and use unique codes (combination of
letters) to denote each variable (incident type). Each incident (event) would
become a row in the database. You may be able to use a Lookup function to
pull any other detail based on the code, i.e., a numerical value, or employee
ID. Depending on the number of employees, you could use a separate area of
the spreadsheet with either VLOOKUP or HLOOKUP (depending on your layout) to
pull together the DSUM side of things.

But, I would consider using MS-Access for this application instead of Excel.
You could extract some of the employee data from a datasource outside Access,
too.

You will need to determine what constitutes a 'key' in the database to
ensure each employee is unique, e.g., employee ID and department, so as not
to have duplicate records of two different employees of the same/similar
name, or those that may work in more than one department.

P.