Attendance Log

Nicholas Pantalone

Hello, I am pretty new to SmartSheets but have been a quick learner so far.

I am try to build an attendance log to track employee that call out and/or are late to work. I have the sheet, form, and the automation. I am asking if there is a way for the automation to send an email if an employee reaches a threshold of call outs or tardies?

For instance, if an employee name is on the list multiple times, and they are within a certain time frame, it was count the occurrences and send an email to the supervisors and the employee to let them know they are either close to or have violated the attendance policy.

Thanks for all the other threads from which I have learned and thanks in advance for any advice/assistance.

Mark Cronk

Hi Nicholas,

Your formula would be along the lines below. Place this column in a checkbox column [trigger]:

=IF(OR(COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Call Out", {date}, @cell <=today(-30))>1, COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Call Out", {date}, @cell <=today(-60))>2, COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Call Out", {date}, @cell <=today(-90))>3, COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Late", {date}, @cell <=today(-30))>3, COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Late", {date}, @cell <=today(-60))>5, COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Late", {date}, @cell <=today(-90))>7), 1, 0)

Work?

Mark

Adjust the column and range names to match your sheets. I assumed you had a column to capture the type of action - late or call out - and that column name was an external range called {action type}.

Mark Cronk

Hi Nicholas,

There are a few ways to do this. Sounds like the simplest is going to be to add 2 columns to your sheet - [call outs] and [tardy]. Place a formula in each column that triggers when someone reaches your notification threshold. It will be a COUNTIFS or SUMIFS the call outs or tardies for the name@row during a given time period are greater than your threshold. The trigger column could be a checkbox or text. Text will let you have thresholds for different warning levels and automation -- if > 4@=Employee. If > 7 =Supervisor. With automation that sends the corresponding notifications.

Make sense?

Mark

Nicholas Pantalone

Hey Mark,

Thank you very much for the help!!! I haven't used COUNTIFS yet, and I relatively new to SmartSheets. I and comfortable that i can make the automation once I get the IF statement worked out, but what would the formula look like? We are using {{Employee Number}} as the item to be counted. The thresholds are more than 1 call out in 30 days, 2 in 60days or 3 in 90 days and more than 3 lates in 30days, 5 in 60 and 7 in 90 days. I just cant wrap my head around that logic.

Mark Cronk

Hi Nicholas,

Your formula would be along the lines below. Place this column in a checkbox column [trigger]:

=IF(OR(COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Call Out", {date}, @cell <=today(-30))>1, COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Call Out", {date}, @cell <=today(-60))>2, COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Call Out", {date}, @cell <=today(-90))>3, COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Late", {date}, @cell <=today(-30))>3, COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Late", {date}, @cell <=today(-60))>5, COUNTIFS({Employee Number}, [employee number]@row, {action type}, @cell="Late", {date}, @cell <=today(-90))>7), 1, 0)

Work?

Mark

Adjust the column and range names to match your sheets. I assumed you had a column to capture the type of action - late or call out - and that column name was an external range called {action type}.