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

**Formulation** · 08-07-2017, 02:27 PM

I've attached a sample sheet. Thanks for anyone who can help!

I have created an attendance tracker which currently adds up points for instances of tardy, call off, etc... I have a formula set to only add points to the total if they were in the previous 12 months.

My question is, I need one last thing added and that is a way to remove 1 point from the total points if they have not been tardy, called out in the last 90 days.

Currently the total points (in the last 12 months) is totaled for each employee starting in Cell H6
If there has been 90 days of no entries (days start in I6 - all the way to the right), I would like to subtract 1 point from the total in column H.

For example, today is August 7th, 2017. If the previous 90 days (May 9th-August 7th) has empty cells, remove 1 point from the total.

I will have to ignore all future dates as they can not be factored into the 90 day period.

**JohnTopley** · 08-07-2017, 03:09 PM

Try

in H6

=E6+F6+G6-IF(SUMPRODUCT(--($I6:$ARH6 <> "")*($I$5:$ARH$5>TODAY()-90)*($I$5:$ARH$5 < TODAY())),0,1)

**Formulation** · 08-07-2017, 03:23 PM

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.

**JeteMc** · 08-07-2017, 08:12 PM

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:

Please Login or Register  to view this content.

*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)))

**FlameRetired** · 08-08-2017, 12:11 AM

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:

Please Login or Register  to view this content.

The final formula in L6

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 08-08-2017, 01:46 AM

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

For the first helper formula

Formula:

Please Login or Register  to view this content.

**Formulation** · 08-08-2017, 10:55 AM

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)

**JeteMc** · 08-08-2017, 05:10 PM

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

Formula:

Please Login or Register  to view this content.

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.

**Formulation** · 08-10-2017, 09:47 AM

Thanks JeteMc! Your help may land me a promotion.

I got everything working perfectly. I even added some vba to auto open at the column with today's date.

I did make a minor change to the 90 day period formula. They work mon-fri but the 90 day period takes into account sat/sun also. I changed the -90 part to -65 and it seems to check out.

**JeteMc** · 08-10-2017, 10:00 AM

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.

**Formulation** · 08-15-2017, 09:00 AM

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).

**Formulation** · 08-15-2017, 03:57 PM

I've tried a few solutions to my problem in post #11 but nothing has worked so far.

Anyone have an idea on how I can accomplish this?

**JeteMc** · 08-15-2017, 04:57 PM

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.

**Formulation** · 08-15-2017, 05:07 PM

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

**JeteMc** · 08-15-2017, 05:53 PM

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

Formula:

Please Login or Register  to view this content.

*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.

**Formulation** · 08-16-2017, 08:58 AM

I set up a scenario in the attachment that hopefully explains the issue better. The formula you posted did solve starting the year with 0 points, going two 90 day periods, then getting 1 point. That formula correctly gives them 1 point instead of taking off two and totaling 0 points.

In row 7 (Jan Doe), I set up a Call off on 1/3/2017 - followed by two 90 day periods of no points - then a call off on 8/15/17.
The result should equal 1 point instead of 2. They would get the first point on 1/3/17, the first 90 day period would bring the total to 0, the second period would be ignored as they are already at zero, then the 8/16/17 call off would bring the total to 1 point.

I appreciate you taking time to figure this out. I'm normally pretty decent with excel but this is beyond my scope and I can't seem to find any guides or similar situations on the internet.

**Formulation** · 08-18-2017, 08:49 AM

I hate to bump but I'm still stuck on post #16.

**Formulation** · 08-21-2017, 09:30 AM

Bump .

**JeteMc** · 08-21-2017, 10:54 AM

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:

Please Login or Register  to view this content.

The formula that populates column I is:

Formula:

Please Login or Register  to view this content.

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.

**Formulation** · 09-14-2017, 09:42 AM

Thanks for your help again JeteMc. That's really close but still not quite there.

Hopefully someone else will chime in despite this thread being multiple pages already. Here are some basic rules and an updated file.

Rolling 365 day point drop off period (working)
Points on count on or past the date of hire (working)
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)

As of now, the spreadsheet adds up points based on a drop down selection in each cell. It's currently set to a 365 day rolling period so any points acquired 366+ days ago will not count.
For every 90 calendar days of perfect attendance (blank cells) = One point will be removed from the total points column for each person.

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.

**Formulation** · 09-15-2017, 12:51 PM

bump up and away we go

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

LinkBack

Thread Tools

Rate This Thread

Display

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

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: Advanced attendance tracker setup

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Re: How can I automatically subtract a number based on previous 90 days (attendance tracke

Thread Information

Users Browsing this Thread

Similar Threads

Need my attendance tracker to count consecutive days as one event instead of individually

How to create an index of dates based on another's value for attendance tracker

Rolling 12 month attendance point tracker - how can the points to fall off after 365 days?

Formula to look back 90 days and drop point on attendance tracker

[SOLVED] Subtract Two Dates By The Number of Days, But Only Inlcude Work Days

Generating the next number automatically based on a previous cell value

Subtract # of days from date, but if not sat, goto previous sat?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions