Attendance record using excel

[Lyday]

I need to help with developing a attendance record using Excel. I will track absences using letter codes placed in cells indicating the date of the occurrence.

We use a point system to track an EE's absences. The system has a range of -2 to +3. A new EE starts out at 0 on the scale, for every 4 months of perfect attendance the EE receives 1 point, maxing out at +3 points.

If the EE misses a day 1 point is deducted for the occurrence. If the employee missed more that one day but for the same reason only 1 point is deducted and counted as 1 occurrence. The EE's work a 12 hour shift, if they are late and report no later than 6 hours in their shift, they are deducted 1/3 of an occurrence, but if they report after 6 hours in their shift they are deducted 2/3's of occurrence.

The same applies in the event the EE leaves early, if the EE leaves sometime from the start of the shift up to 6 hours; the EE receives 2/3's of an occurrence. If the EE leaves early after 6 hours of work he is charged with 1/3 of an occurrence.

Each occurrence stays on the record for 4 months.

Example:
• EE is at zero (0) on point system and he misses one day (1 occurrence), the EE would move backwards from zero (0) to minus one (-1) on the scale.
• EE was at zero (0) but instead he had a four month period without an occurrence, then the EE would move forward from zero (0) to a plus on (+1) on the scale.
I understand this is quite lengthy but I need help in tracking this, because pay is involved in this process.

I have attached a record I am using to give you an idea of what I am trying to do. Any help is greatly appreciated.

Codes I am using for occurrences:
• A - Absent/No Call - 1 occurrence
• S - Sick - 1 occurrence
• LE1 - Left Early > 6 hrs - 1/3 of an occurrence
• LE2 - Left Early < 6 hrs - 2/3 of an occurrence
• T1 - Tardy < 6 hrs - 1/3 of an occurrence
• T2 - Tardy > 6 hours - 2/3 of an occurrence

[p24leclerc]

It's a nice sheet you built there. What is the total column AY? Why don't you change your formula that counts the number of T1 by -1/3 as this is the amount of point the EE looses. If you do this for all the summary columns, you'll end up with the total of lost points.
Where is the starting point of EE stored?