Attendance Record file with a 6 months rolling points calculation

Hello Excel Forum Team!

I have been assigned to create an excel file that tracks the attendance record of the employees. This will be applied to a call center. These are the guidelines to follow:

Absence periods from work are evaluated using a point-based system. No more than six (6) points can be accumulated during a rolling six (6) month period—Points ¨expire¨ or ¨roll-off¨ monthly (example follows).

Regular Absence - 2 points
Critical work / blackout days absence - 3 points
NCNS - 3 points
Tardy - 0.25 points are incurred every 15 minutes up to 1.0 point
Leave Early - 0.25 points are incurred every 15 minutes up to 1.0 point

Examples of a six Month Rolling Period Showing Points that ¨Expire¨ or ¨Roll-off¨:
 Month 1 – July: 1 point for 1 day of Absence = 1 point Total
 Month 2 – August: 0 points = 1 point Total
 Month 3 – September: .5 points for Tardy + 1 point for 1 day of absence = 2.5 points Total
 Month 4 – October: 1.0 points for 1 day absence = 3.5 points Total
 Month 5 – November : .5 points for Tardy + 1.0 points for 1 day absence = 5.0 points Total
 Month 6 – December : 0 points = 5 points Total
 Month 1 – January = there were 5.0 points at the end of Month 6 (December), but 1 point rolled off from Month 1 (July) leaving a Total of 4.0 points to start June (new period begins and the first month´s points are ¨rolled off¨ or expired)
 Month 2 – February = .5 points incurred for Tardy + 4.0 points = 4.5 points Total
 Month 3 – March = 1.0 point incurred for 1 day absence = 5.5 points Total
 And so on based on the activity in each month – points simply roll off one month at a time

The problem I am having is that I do not know where to start or what to do first. I know about excel formulas but I cannot think of one to automate the report. If there is a template that would be great

Any help you can provide is highly appreciated

Thanks!

See if this interests you
Three tables are drawn: the table occurrence of records [A4: E ..], the table points [G4: I9] and the table of employees [K4: T ..].
One formula is available to calculate the number of points depending on the occurrence

Formula:

Please Login or Register  to view this content.

and another formula to determine the cumulative values of each employee

Formula:

Please Login or Register  to view this content.

See the file Absence.xlsx
I hope this helps.

Thank you very much for taking your time to help me. It does give me a guideline and overall idea of where to start. I will be sharing the file with you as soon as I have it ready

Again Thank you Jose Agusto