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!

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

Are you able to post a screenshot with confidential information removed?

Carson Penticuff

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)))))

Paul Newcome

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)))

Robin Soprano

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.

Robin Soprano

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))

Robin Soprano

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)))))

Hollie Green

What if you add a checkbox column for called out previous shift and add a reference to that for your formula.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.