Formula Help!!
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)
- Collect the names of the employees (Without duplicates)
- Count how many times they called out within last 12 months of TODAY's date that are "Unexcused"
- 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}))
Answers
-
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?
-
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)
-
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?
-
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)))
-
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.
-
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?
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!