One of the managers at my employer is trying to implement a new "attendance tracker" in Excel based on new attendance criteria we are implementing.
The basic concept of the policy is: Leaving Early or Calling Out garner 1 occurrence, being tardy garners 1/3 of one occurrence (so 3 tardy = 1 occurrence). 2 Occurrences earn the employee a written warning. Each occurrence sets a "trigger date" which begins a "90 day rolling period" of time, after which all occurrences disappear as if they've never happened. However, if you earn another occurrence in this time frame it adds an additional 90 days with the date of the latest occurrence being the new "trigger date." After 90 days occurrence free, your current occurrences are wiped clean.
Because there is this rolling 90 day period
Hopefully that makes sense...
So here is the dilemma. He wants the spreadsheet to do the following:
If there is no trigger date, then base the count on the Start Date through Today's date. - This is done.
If there is a trigger date, base the count on the "Start Date" through the "End Date". The "End Date" varies based on cell B29 and whether it is "First," "Second," or "Third."
After row 33 on the spreadsheet is just a running list of dates in Column A and in Column B we notate "Late" "Early" or "Out". We cannot seem to make it count only through the date ranges mentioned above rather than counting from B33 down.
Below is the layout of the spreadsheet.
Thanks!
The basic concept of the policy is: Leaving Early or Calling Out garner 1 occurrence, being tardy garners 1/3 of one occurrence (so 3 tardy = 1 occurrence). 2 Occurrences earn the employee a written warning. Each occurrence sets a "trigger date" which begins a "90 day rolling period" of time, after which all occurrences disappear as if they've never happened. However, if you earn another occurrence in this time frame it adds an additional 90 days with the date of the latest occurrence being the new "trigger date." After 90 days occurrence free, your current occurrences are wiped clean.
Because there is this rolling 90 day period
Hopefully that makes sense...
So here is the dilemma. He wants the spreadsheet to do the following:
If there is no trigger date, then base the count on the Start Date through Today's date. - This is done.
If there is a trigger date, base the count on the "Start Date" through the "End Date". The "End Date" varies based on cell B29 and whether it is "First," "Second," or "Third."
After row 33 on the spreadsheet is just a running list of dates in Column A and in Column B we notate "Late" "Early" or "Out". We cannot seem to make it count only through the date ranges mentioned above rather than counting from B33 down.
Below is the layout of the spreadsheet.
Thanks!
Rich (BB code):
A B
6 Attendance Standings 1st Written Warning
7 LATE (arrival) 1
8 LEAVE (early) 1
9 (called) OUT 1
10 TTL Occurrences 2.3
11
25
26 Start Date 10/24/15
27 Today 1/22/16
28 Trigger Date
29 Type
30 End Date
31
32 Agent Name
33 01/01/16
34 01/02/16 LATE
35 01/03/16 EARLY
36 01/04/16 OUT
37 01/05/16
38 01/06/16