formulation
09-19-2017, 07:16 AM
I've attached a copy of sample date and disabled a macro I have to go to the current date in row 5.
20396
I've spent a decent amount of time on this spreadsheet to total attendance based on a few variables. I've been stuck for a month on the last feature.
I have set up a situation in the sheet and explained it below, that should hopefully explain the issue better once you take a look.
For every 90 calendar days of perfect attendance (blank cells) = One point will be removed from the total points column for each person.
*However, if an employee's total points is at 0 at any given time, blank cells(perfect attendance) should be ignored. 0 points is the minimum.
Rolling 365 day point drop off period (working) (Uses cell ADO:6)
Points only count on or past the date of hire (working) (Cell ADN:6)
Each cell has a drop down with selections for tardy (TD), call off (CO), Leave Early (LE), Continuous Absence (CA).
TD = .5 Points |||| CO = 1 Point |||| LE/CA can be ignored (0 points)
Currently everything is working as intended except for one feature, the 90 day perfect attendance reward.
Weekends are excluded from the dates in row 5 so anytime I refer to 90 days it's 65 cells. (not perfect but it works for our use)
I set up a situation in the above file that shows the problem. In row 6, they receive a call off (CO) on 1/3/2017 (1 point)
Then they have two 90 day periods of blank cells. Followed by a call off on 8/15/2017.
This should result in 1 point instead of 2 points.
1/3/2017 = 1 point First 90 day period of blank cells = 0 points Now that employee has 0 points, the second 90 day blank cell period should be ignored as they can't go below 0. 8/15/2017 call off should bring total back to 1 point.
*other rules in place already... Any cells with a drop down selection before their date of hire are ignored.
If they have 0 points and then (5) 90 day perfect attendance periods in a row, then a call off, the total should be 1 point.https://www.excelforum.com/images/misc/paperclip.png Attached Files
20396
I've spent a decent amount of time on this spreadsheet to total attendance based on a few variables. I've been stuck for a month on the last feature.
I have set up a situation in the sheet and explained it below, that should hopefully explain the issue better once you take a look.
For every 90 calendar days of perfect attendance (blank cells) = One point will be removed from the total points column for each person.
*However, if an employee's total points is at 0 at any given time, blank cells(perfect attendance) should be ignored. 0 points is the minimum.
Rolling 365 day point drop off period (working) (Uses cell ADO:6)
Points only count on or past the date of hire (working) (Cell ADN:6)
Each cell has a drop down with selections for tardy (TD), call off (CO), Leave Early (LE), Continuous Absence (CA).
TD = .5 Points |||| CO = 1 Point |||| LE/CA can be ignored (0 points)
Currently everything is working as intended except for one feature, the 90 day perfect attendance reward.
Weekends are excluded from the dates in row 5 so anytime I refer to 90 days it's 65 cells. (not perfect but it works for our use)
I set up a situation in the above file that shows the problem. In row 6, they receive a call off (CO) on 1/3/2017 (1 point)
Then they have two 90 day periods of blank cells. Followed by a call off on 8/15/2017.
This should result in 1 point instead of 2 points.
1/3/2017 = 1 point First 90 day period of blank cells = 0 points Now that employee has 0 points, the second 90 day blank cell period should be ignored as they can't go below 0. 8/15/2017 call off should bring total back to 1 point.
*other rules in place already... Any cells with a drop down selection before their date of hire are ignored.
If they have 0 points and then (5) 90 day perfect attendance periods in a row, then a call off, the total should be 1 point.https://www.excelforum.com/images/misc/paperclip.png Attached Files