How can I automatically subtract a number based on previous 90 days (attendance tracker)

Thanks John. That works great. However... I should of explained better.

Lets say John Doe has accumulated 5 points from Jan 1st - March 1st.
If March 2nd - (90 days in future), John accumulates 0 points. Johns total should now be 4 points.
For John to remove another point from the new total of 4. John would need another 90 days after the previous 90 day period of 0 points.

The above formula works but it is only checking the previous 90 days.
I need something that will subtract 1 for every instance of 90 days.

Hopefully that makes sense.

This proposed solution employs three helper columns, any of which may be hidden for aesthetic purposes.
The first helper column (XFC) displays the location of the first date in the row using the formula: =ADDRESS(ROW(),10)
The second helper column (XFD) displays the location of the last date in the row using the formula: =ADDRESS(ROW(),MATCH(E$3,A$5:ARH$5,0))
The third helper column (H) displays the number of consecutive 90 day periods without an absence, using the array entered formula*:

Formula:

=SUM(INT(FREQUENCY(IF(INDIRECT(XFC6&":"&XFD6)="",COLUMN(INDIRECT(XFC6&":"&XFD6))),IF(INDIRECT(XFC6&":"&XFD6)<>"",COLUMN(INDIRECT(XFC6&":"&XFD6))))/90))

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
I assume that you would not want the 'Total Points' to be less than zero, so I modified that formula to read: =MAX(0,E6+F6+G6-H6)
Let us know if you have any questions.
EDIT: change the formula in XFC2 and down to read: =ADDRESS(ROW(),MAX(10,IFERROR(MATCH(TODAY()-365,J$5:ARH$5,0),FALSE)))

Originally Posted by JeteMc

This proposed solution employs three helper columns, any of which may be hidden for aesthetic purposes.

Thank you all for coming up with a solution. This seems to work. Is there a way around the following problem... (updated attachment showing issues below)

Today is 8/8/2017. If someone was hired on say, 8/1/2017. On 8/2/2017 they have a NCNS (5 points). (row 6)
Currently with your solution, their total points are at 4 instead of 5. Is it feasible to only start at the employees date of hire?

Another example: Hired 1/1/2017, NCNS on 8/7/2017 (5 points). 1/1/2017 - 8/6/2017 = 218 days perfect attendance. (Total points should be 3 due to having two 90 day periods perfect). Currently this example totals 4 points. (row 8)

This proposal uses 4 helper columns. Each calculates a separate 90 period. Here is the first formula for the most recent 90 day look back in the new column H. The others shift the range back 90 days each.

Formula:

=IFERROR(--(SUMPRODUCT(NETWORKDAYS.INTL(+INDEX($M$5:$ARK$5,MATCH($E$3-90,$M$5:$ARK$5,0)):

INDEX($M$5:$ARK$5,MATCH($E$3-0,$M$5:$ARK$5,0)),+INDEX($M$5:$ARK$5,MATCH($E$3-90,$M$5:$ARK$5,0)):

INDEX($M$5:$ARK$5,MATCH($E$3-0,$M$5:$ARK$5,0)),"0000000")*(INDEX($M6:$ARK6,MATCH($E$3-90,$M$5:$ARK$5,0)):

INDEX($M6:$ARK6,MATCH($E$3-0,$M$5:$ARK$5,0))=Reasons))<1),0)

The final formula in L6

Formula:

=E6+F6+G6-H6-I6-J6-K6

Disregard my previous "helper" formula. I over complicated it a lot.

For the first helper formula

Formula:

=IFERROR(--(SUMPRODUCT(INDEX($M$5:$ARK$5,MATCH($E$3-90,$M$5:$ARK$5,0)):

INDEX($M$5:$ARK$5,MATCH($E$3-0,$M$5:$ARK$5,0))*(INDEX($M6:$ARK6,MATCH($E$3-90,$M$5:$ARK$5,0)):

INDEX($M6:$ARK6,MATCH($E$3-0,$M$5:$ARK$5,0))=Reasons))=0),0)

Looking at the solution proposed in post #4 the formula in column XFC is modified to read:

Formula:

=ADDRESS(ROW(),MAX(10,IFERROR(MATCH(TODAY()-365,A$5:ARH$5,0),FALSE),IFERROR(MATCH(A6,A$5:ARH$5,0),FALSE)))

The two scenarios that you mention are set up in rows 10 and 11 (Mark Reed and John Tolkien)
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you get the promotion and have a blessed day.

I'm attempting to fix one small issue that I hope one of you gurus can figure out. (latest copy is attached)

At the moment if someone starts the year with 0 points and goes 180 days with perfect attendance, they still have 0 points.
However, those two 90 day periods of perfect attendance do not need to count.

For instance, Jan 1st = 0 points. Perfect attendance for 180 days. Then in August they call off (1 point). They should still only have 1 point until 90 days perfect attendance past the date of the call off in August.

If they start the year with 1 point, go 180 days with perfect attendance, they should have 0 points since the minimum is set to 0(that part works). Now if they get a point in say October their total should be 1 point. Currently it's taking into account the 180 days of perfect attendance at the beginning of the year and subtracting 1 point (it shouldn't do that).

I am not able to work with the file attached to post #11 when I try to make a change the array entered formula in column ADN I get a popup stating 'Can not modify or erase arrays in shared workbooks'. Not saying that changing that formula will work, but need to have all options available.

Sorry about that. I updated the attachment in post #11 so that the workbook isn't shared.

See if the following modification to the array entered formula* in ADN6 and down solves the problem:

Formula:

=ADDRESS(ROW(),MAX(10,IFERROR(MATCH(TODAY()-365,A$5:ADL$5,0),FALSE),IFERROR(MATCH(A6,A$5:ADL$5,0),FALSE),MAX((J6:ADL6<>"")*(COLUMN(J6:ADL6)))))

*Remember to simultaneously press the Ctrl, Shift and Enter keys before attempting to copy down.
If this doesn't work then please set up a scenario showing the problem and tell us the values that you expect to see in columns H and I for that scenario.
I tested this by giving Jan Doe a Call Off absence on 5/1/17 and the Evaluate Formula shows that she would have only accumulated credited days afterwards (which I believe is what you want).
Let us know if you have any questions.

I don't think that this will do exactly what you want, however it is as close as I have been able to come and I have worked on it off and on since the 16th.
In row 7 of the attached file, the formula for column H has been removed and the calculation is instead accomplished by Helper columns (ADQ:BHR)
The formula that populates the helper columns is:

Formula:

=IF(OR(K7="CO",K7="LE"),SUM(ADP7,1),IF(K7="TD",SUM(ADP7,0.5),MAX(0,ROUND(ADP7-1/90,3))))

The formula that populates column I is:

Formula:

=MROUND(INDEX(ADQ7:BHR7,MATCH(C$3,ADQ$5:BHR$5,0)),0.5)

In the attached file Jan Doe receives a 'CO' on 1/3. Before the 90 days have passed she receives a 'TD' on 5/1 which doesn't completely reset the total points, but does add half a point to the cumulative points in the helper. On 8/15 she receives a 'CO' which at that time does start a new points countdown.
I think that you can see one of the drawbacks by deleting the 'CO' for 8/15 and placing it on 6/15 instead. The total points will report 0.5 instead of 1.
You can see another if you replace the 'CO' in 6/15 with a 'TD'. The tardy reduces to a zero in 45 days instead of 90 (as a tardy is only .5 points).
It is hard for me to come up with the mathematical logic to accomplish what you are asking. Perhaps if you or the person who is implementing this 'accumulated points' policy could clearly define the parameters in mathematical terms someone could come come up with a solution.