Consulting

Results 1 to 9 of 9

Thread: Attendance Spreadsheet - Custom point reduction

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Lightbulb Attendance Spreadsheet - Custom point reduction

    I've attached a copy of sample date and disabled a macro I have to go to the current date in row 5.
    Copy of Legrand Attendance99999.xlsm
    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. Attached Files
    Last edited by formulation; 09-19-2017 at 07:44 AM.

Tags for this Thread

Posting Permissions

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