Subtracting after a certain time frame

I am creating an Attendance Record for my employees. I have it to where any infraction, they would accumulate points and every month it would add points from the previous months. My problem is that I need to subtract a point after 3 months.

Attached is the spreadsheet that I made.

Attendance Record - Test.xlsx

Originally Posted by 6StringJazzer

Does this mean that any point assessed is dropped 3 months from the day it was assessed? So you don't really mean "a point"? For example, if there was a no-show on June 15, there are 5 points on the record until September 15. Is that right?

Putting your data on a different sheet for each month makes this more difficult. Are you locked into this format? It would be much better to have all data on a single sheet.

No, only 1 point is subtracted. Ex. No Show in June is 5 points, no other points are accumulated within the next 3 months so in September 1 point is subtracted leaving 4 points left in the books.

As for the format, I only did it that way for my convenience. It would be easier for me to view at month to month as opposed to scrolling for the month. But I am open to any ideas.

I have made the following changes:

The first three months must be set up manually because there is no month three months prior to drop a point for
Each month's Cumulative is calculated as the current month's point plus the previous months cumulative. Then if there were any points from three months prior, one point is dropped.
These formulas are determined based on the month name
I made June the same as the other sheets for consistency, i.e., included Cumulative Points even though it's the first month
Changed cell B1 in each month to be a date value, not just the month name as a string.

Future months can be set up by copying the September sheet to create a new one.

What you attached is not based on the solution I provided. It looks like your original file.

Originally Posted by Dahonu

would not calculate correctly.

Looks OK to me. You'll have to be more specific about what you are expecting, and what you think is wrong.

I think it is probably a communication gap on how you want points dropped. You said "I need to subtract a point after 3 months". So after three months, the 2 points in June becomes 1 point. Similar to the points on the last two rows.

I suspect that you have something in mind where the 2 points in June are eventually completely dropped but you haven't described exactly how you want that to happen.

Point taken.

So, the 2 points in June will become 1 point in September then 0 points in October. And if incase they do accumulate more points then that gets added then those won't get subtracted until the 3 months... is that even possible?

Your suspicion is correct, eventually the points will be zeroed out with the exception that they won't accumulate points.

I think that is doable but having to span across worksheets instead of just using dates make it a little tricky. I will take a look as time permits, unless someone else jumps in.

I don't know how to do this keeping each month in its own tab without an overhaul of the design. Points have to be aged--that is, you have to know how old each point is to be able to drop it at the appropriate time. This requires a bit more thought than I originally expected.

Originally Posted by 6StringJazzer

I don't know how to do this keeping each month in its own tab without an overhaul of the design. Points have to be aged--that is, you have to know how old each point is to be able to drop it at the appropriate time. This requires a bit more thought than I originally expected.

If it needs to be on one sheet, that's fine. It was more of a convenience for me.