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 occurence.
We use a point system to track an EE's absences. The system has a range of -2 to +3. A new employee 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 occurence. If the employee missed more that one day but for the same reason only 1 point is deducted and counted as 1 occurence. 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 occurence, but if they report after 6 hours in their shift they are deducted 2/3's of occurence.
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 occurence. If the EE leaves early after 6 hours of work the are charged with 1/3 of an occurence.
Each occurence stays on the record for 4 months.
Example:
Codes I am using for occurences:
We use a point system to track an EE's absences. The system has a range of -2 to +3. A new employee 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 occurence. If the employee missed more that one day but for the same reason only 1 point is deducted and counted as 1 occurence. 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 occurence, but if they report after 6 hours in their shift they are deducted 2/3's of occurence.
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 occurence. If the EE leaves early after 6 hours of work the are charged with 1/3 of an occurence.
Each occurence stays on the record for 4 months.
Example:
- EE is at zero(0) on point system and he misses one day (1 occurence), 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 occurence, then the EE would move forward from zero (0) to a plus on (+1) on the scale.
Codes I am using for occurences:
- A - Absent/No Call - 1 occurence
- S - Sick - 1 occurence
- LE1 - Left Early > 6 hrs - 1/3 of an occurence
- LE2 - Left Early < 6 hrs - 2/3 of an occurence
- T1 - Tardy < 6 hrs - 1/3 of an occurence
- T2 - Tardy > 6 hours - 2/3 of an occurence