Formula help - COUNTIF consecutive dates
We use a smartsheet form for staff call outs. We've implemented a point system for disciplinary action. A call out or absence is 1 point, a tardy is 1/2 point. I've worked out the math to count the point value of the row -0 if "excused" is checked.
However, consecutive days if calling out sick are considered one absence. I need to be able to consider if this employee has a callout on a previous day for reason "sick". I'm stumped as to how to filter for same employee in a formula since there would be other records
Thanks!
Answers
-
Are you able to post a screenshot with confidential information removed?
-
Here is the general concept.
=IF(AND([Name]@row <> "", ISDATE([Date]@row), [Type]@row <> ""), IF([Type]@row = "Excused", 0, IF([Type]@row = "Tardy", 0.5, IF([Type]@row = "Unexcused", IF(COUNTIFS([Name]:[Name], [Name]@row, [Type]:[Type], "Unexcused", [Date]:[Date], [Date]@row - 1) > 0, 0, 1)))))
-
I would use something like this:
IF([Absence Type]@row = "Tardy", 0.5, IF([Absence Type]@row = "Unexcused", IF(COUNTIFS(Employee:Employee, @cell = Employee@row, [Absence Type]:[Absence Type], @cell = "Unexcused", Date:Date, @cell = WORKDAY(Date@row, -1)) = 0, 1)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
These are very helpful - I do need the first day of sickness to count but beyond that if consecutive it doesn't add additional infractions.
-
Pertinent fields are:
Name
Date of Absence
Reason Provided - "sick" goes here.
Excused is a checkbox
Notes/Remarks - this is where a tardy is recorded
Points - so far I have this formula
=IF(Excused@row = true, 0, IF([Notes/Remarks]@row = "Tardy", 0.5, IF([Notes/Remarks]@row = "No Call, No Show", 1, 0)) + IF(ISBLANK([Reason Provided]@row), 0, 1))
-
I'm close....but still not adjusting if the callout for "sick" is consecutive days. And actually they can call out for multiple days - if scheduled a double - that should only ding them as one infraction.
Here's where I am now - the last part needs help... please
=IF(Excused@row = true, 0, IF([Notes/Remarks]@row = "Tardy", 0.5, IF([Notes/Remarks]@row = "No Call, No Show", 1, IF(ISBLANK([Reason Provided]@row), 0, IF(COUNTIFS(Name:Name, Name@row, [Reason Provided]:[Reason Provided], "Sick", [Date of Absence]:[Date of Absence], [Date of Absence]@row - 1) > 1, 0, 1)))))
-
What if you add a checkbox column for called out previous shift and add a reference to that for your formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!