+ Reply to Thread
Results 1 to 21 of 21

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

  1. #1
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,121

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

    Try

    in H6

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

  3. #3
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

    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.
    Last edited by Formulation; 08-07-2017 at 03:58 PM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

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

    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: copy to clipboard
    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)))
    Attached Files Attached Files
    Last edited by JeteMc; 08-07-2017 at 08:28 PM. Reason: Added Edit
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,039

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

    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: copy to clipboard
    Please Login or Register  to view this content.



    The final formula in L6
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,039

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

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

    For the first helper formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

    Quote Originally Posted by JeteMc View Post
    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)
    Attached Files Attached Files
    Last edited by AliGW; 08-15-2017 at 05:11 PM. Reason: Unnecessarily long quotation shortened.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

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

    Looking at the solution proposed in post #4 the formula in column XFC is modified to read:
    Formula: copy to clipboard
    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.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

    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.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

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

    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.

  11. #11
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

    Lightbulb Re: Advanced attendance tracker setup

    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).
    Attached Files Attached Files
    Last edited by Formulation; 08-15-2017 at 05:06 PM.

  12. #12
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

    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?

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

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

    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.

  14. #14
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

    Sorry about that. I updated the attachment in post #11 so that the workbook isn't shared.
    Last edited by AliGW; 08-15-2017 at 05:08 PM. Reason: Unnecessary quotation removed.

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

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

    See if the following modification to the array entered formula* in ADN6 and down solves the problem:
    Formula: copy to clipboard
    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.

  16. #16
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

    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.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

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

  18. #18
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

    Bump .

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

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

    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: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates column I is:
    Formula: copy to clipboard
    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.
    Attached Files Attached Files
    Last edited by JeteMc; 08-21-2017 at 10:57 AM.

  20. #20
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

    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.
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    08-26-2016
    Location
    usa
    MS-Off Ver
    14.0.4760.1000
    Posts
    25

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

    bump up and away we go

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 11-02-2016, 09:52 AM
  2. Replies: 0
    Last Post: 09-16-2016, 08:54 AM
  3. Replies: 1
    Last Post: 10-24-2014, 09:57 PM
  4. Formula to look back 90 days and drop point on attendance tracker
    By tmorr24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-22-2014, 01:27 AM
  5. [SOLVED] Subtract Two Dates By The Number of Days, But Only Inlcude Work Days
    By ptho16 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2012, 11:31 AM
  6. Generating the next number automatically based on a previous cell value
    By hpfeiffer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2012, 03:04 AM
  7. [SOLVED] Subtract # of days from date, but if not sat, goto previous sat?
    By Fernando in forum Excel General
    Replies: 3
    Last Post: 05-01-2006, 03:57 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1