Formula Help!!

rtahtinen

I would like to "Count" how many days a person has called out that are "Unexcused" in the last 12 months on our active spreadsheet. Anytime we have an employee that calls out on our line a "Form" is filled out that helps us track who called, date they called, time they called, reason for call, whether it is excused, pending, or unexcused. On a SEPARATE sheet I am trying to do the following: (Different Columns)

1. Collect the names of the employees (Without duplicates)
2. Count how many times they called out within last 12 months of TODAY's date that are "Unexcused"
3. Count how many times they were tardy in a "Rolling" 30-day period for the last 12 months

Is this something that you can help me with? I tried a couple of versions of formulas to no luck.

=JOIN(COLLECT(COUNTM({Attendance Employee Form Range 4}), {Attendance Employee Form Range 3}, "Unexcused", {Attendance Employee Form Range 5}, ISDATE(IF(MONTH(TODAY()), <12, "-")), {Attendance Employee Form Range 2}))

Ryan Sides

Hi @rtahtinen

1) Smartsheet isn't going to give you the list of the names on your second sheet. You'll need to fill those in.

2) I setup a mock version of yours. You'll need to change the references in your formula to match yours but see below.

3) Do you mean "rolling" by month? Or truly 30 days like 9/13 to today?

Does that help?

rtahtinen

So it would be a rolling 30 days throughout....

Example: January 1st to January 30th; January 2nd to January 31st; January 3rd to February 1st and so on and so forth (Within 12 months from TODAYs date)

rtahtinen

what if you want to use only part of the name rather than all of it? Meaning one sheet has the full name of first and last in one cell and the other sheet has last name and first name in separate cells?? Also, I have another column that is specific to call outs versus tardies so I am needing to only count the column with call out that are NOT blank how will that work in the formula?

rtahtinen

So, I put the one formula together to create how many absences are in 365 days for an employee that is "Unexcused" and it works. I just need help to ADD the (3) consecutive tardies for a rolling "Moving" date within 30 days. If someone can help me add that to the below formula that would be helpful.

=COUNTIFS({Employee Name}, CONTAINS([Last Name]@row, @cell), {Employee Name}, CONTAINS([Preferred Name]@row, @cell), {Date Called Out}, >=TODAY(-365), {Attendance Status}, "Unexcused", {Tardies}, NOT(ISBLANK(@cell)))

Paul Newcome

There are ways to get the non-duplicated list of people generated on your second sheet.

As for getting the bit surrounding 3 tardies within a rolling 30 days, you would need to include a helper column on the source sheet and have a formula there.

rtahtinen

That is a good idea, but what type of formula would help for the 3 tardies within a 30 day rolling period on the source sheet?

Paul Newcome

You would use something similar to your COUNTIFS then wrap it in an IF statement that says if the count is greater than 3, flag.

Something along these lines should get you started:

=COUNTIFS([Date Column]:[Date Column], AND(@cell>= [Date Column]@row - 30, @cell<= [Date Column]@row + 60), Name:Name, @cell = Name@row)