Hello, I got a quick question for you amazing people. Long story short, I'm trying to create an attendance tracker that tracks things like tardiness, call outs, pto, fmla, and overtime. I feel like I got something substantial, but I'm missing a small component.
So the idea is that we track all these different occurrences and have a rolling 365 day total so we don't have to track them falling off everyday. The issue I'm running into is that we allow the employees to pick up overtime shifts to erase a point. We have 3 different values associated with Overtime: Weekday, Weekend, Holiday.
So my vision is to be able to select an occurrence from the "Occurrence column" and have it add to the rolling total.
Let's look at my formulas. Let me first state that I've only included 1 version of overtime and it's value is "1".
Occurrence Point Value
=IF(Occurrence@row = "Tardy (up to 15 minutes)", 0.4, IF(Occurrence@row = "Late (more than 15 minutes)", 0.4, IF(Occurrence@row = "Leave Early less than half shift", 1, IF(Occurrence@row = "Leave Early more than half shift", 0.4, IF(Occurrence@row = "Unscheduled Absence", 1, IF(Occurrence@row = "Unscheduled Absence on a Denied Day Off", 2, IF(Occurrence@row = "No Call No Show", 4.5)))))))
I had an additional if statement in here that was IF(Occurrence@row = "Overtime", "-1"), but this did nothing. I wasn't sure why that wasn't counting, so I decided to create an overtime column
Overtime
=IF(Occurrence@row = "Overtime", "1")
Simply put, would just show the value of the OT shift.
Occurrence Sum
=SUMIFS([Occurrence Point Value]:[Occurrence Point Value], [Employee Name]:[Employee Name], @cell = [Communicator Name]@row, [Occurrence Date]:[Occurrence Date], @cell >= TODAY(-365))
This is my rolling point total. When I had that Overtime IF Statement (inside the Point Value column) the total would not change. So this led me to my new idea, which is:
Occurrence Sum w/ Adjusted Overtime
=[Occurrence Sum]@row - Overtime@row
This is where I'm getting an #INVALID OPERATION, but I am not sure why. It should just be a simple Column A - Column B kind of thing.
Final thought:
If I could skip the creation of both and Overtime and Occurrence Sum w/Adjusted Overtime column, I would appreciate it, but I get the feeling that smartsheet is doesn't want to play nice with me.
Any help is greatly appreciated.
