Excel Excel attendance record

Joined
May 3, 2011
Messages
1
Reaction score
0
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:
  • 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.
I understand this is quite lengthy but I am 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 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
 

Attachments

  • EE record.zip
    9.7 KB · Views: 711

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top