Attendance Point Tracking - Referencing a previous entry

Dan B.

About a year ago, I threw together a janky system to keep track of employee attendance. The idea is that there are different point values assigned to each kind of attendance occurrence, which the points fall off after a year. However, we have added a twist that is throwing me for loop. We now have a policy where if they pick up overtime, they can get a certain amount of point reductions depending on the shift. So this has kind of ruined my set up as when the OT drops, it adds points back to their total, so I need to rewrite a cleaner formula set up.

So here is my sheet. I have over 100 employees, but I've filtered it down to 1 person to make it easier to look at.

So I think the Occurrence and Occurrence Point Value columns should be good, as it's just nested IF formulas. I think the issue is the Total Occurrence Values, as it shows the current value in each cell in it's column. I think if we can have it reference a previous total, so that way the OT value can just be subtracted from it. Here is my current formula for the Total Occurrence Value column.

=SUMIFS([Overtime]:[Overtime], [Communicator Name]:[Communicator Name], @cell = [Communicator Name]@row, [Occurrence Date]:[Occurrence Date], @cell >= TODAY(-365))))

How would you recommend I rewrite it?

Julie Fortney

@Dan B. Something like this?

I added a helper column with a formula to return the most recent entry for that Communicator -

=MAX(COLLECT([Occurrence Date]:[Occurrence Date], [Communicator Name]:[Communicator Name], @cell = [Communicator Name]@row))

Then updated the last three column formulas so the totals will only be calculated on the Communicator's most recent entry

Total Occurrence Values

=IF([Occurrence Date]@row = [Most Recent Entry for Communicator]@row, SUMIFS([Occurrence Point Value]:[Occurrence Point Value], [Communicator Name]:[Communicator Name], @cell = [Communicator Name]@row, [Occurrence Date]:[Occurrence Date], @cell >= TODAY(-365)))

Overtime Total

=IF([Occurrence Date]@row = [Most Recent Entry for Communicator]@row, SUMIFS(Overtime:Overtime, [Communicator Name]:[Communicator Name], @cell = [Communicator Name]@row, [Occurrence Date]:[Occurrence Date], @cell >= TODAY(-365)))

True Total

=IF([Occurrence Date]@row = [Most Recent Entry for Communicator]@row, [Total Occurrence Values]@row - [Overtime Total]@row)

www.linkedin.com/in/julie-fortney-pmp-lssblackbelt

Julie Fortney

Hi @Dan B., how are you reporting these numbers? Based on what I understand of your process and my own experience, here is what I would do:

Create a metric sheet with a row for each team member and columns for the Current Year Total Occurrence Values, Current Year Overtime Total, and Current Year True Total, using Index/Match formulas that calculate those values for each team member.

Another even easier option (depending on how these values are reported) would be a report grouped by team member name and summarized by a sum of each of those "Current Year..." columns I mentioned above. You could filter it for rows with dates in the past 365 days.

If you'd like more details on either of these suggestions, just let me know.

www.linkedin.com/in/julie-fortney-pmp-lssblackbelt

Dan B.

Hi Julie,

I have a question for you. My tracker needs to do a weird thing with overtime points. Our issue is that Overtime points need to be treated differently from their occurrence points. Overtime needs to be one-off formula where its just a simple formula where it searches the most recent entry for that selected employee and subtracts overtime points from their previous total.

I do like the idea of a separate sheet that will display the totals, but as of right now, we run a report like your second suggestion.

Julie Fortney

@Dan B. Something like this?

I added a helper column with a formula to return the most recent entry for that Communicator -

=MAX(COLLECT([Occurrence Date]:[Occurrence Date], [Communicator Name]:[Communicator Name], @cell = [Communicator Name]@row))

Then updated the last three column formulas so the totals will only be calculated on the Communicator's most recent entry

Total Occurrence Values

=IF([Occurrence Date]@row = [Most Recent Entry for Communicator]@row, SUMIFS([Occurrence Point Value]:[Occurrence Point Value], [Communicator Name]:[Communicator Name], @cell = [Communicator Name]@row, [Occurrence Date]:[Occurrence Date], @cell >= TODAY(-365)))

Overtime Total

=IF([Occurrence Date]@row = [Most Recent Entry for Communicator]@row, SUMIFS(Overtime:Overtime, [Communicator Name]:[Communicator Name], @cell = [Communicator Name]@row, [Occurrence Date]:[Occurrence Date], @cell >= TODAY(-365)))

True Total

=IF([Occurrence Date]@row = [Most Recent Entry for Communicator]@row, [Total Occurrence Values]@row - [Overtime Total]@row)

www.linkedin.com/in/julie-fortney-pmp-lssblackbelt

Dan B.

@Julie Fortney

I think this will work, but I want to make sure that when the OT points fall off (after a year passes), that the True total will not change (i.e. add the inverse of OT points).

I've also added to your True Total a MAX( , 0) formula so that the true total can't go below zero. Do you think that will interfere with the calculations?

=MAX(IF([Occurrence Date]@row = [Most Recent Entry for Communicator]@row, [Total Occurrence Values]@row - [Overtime Total]@row), 0)

Julie Fortney

@Dan B.

As written, the occurrence values and overtime points are only pulled into the calculation if they occurred in the past year.

I tested out your MAX formula on a sheet, and it looks like it will function correctly. Great idea!

www.linkedin.com/in/julie-fortney-pmp-lssblackbelt