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



  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

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

  • Carson Penticuff
    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
    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)))

  • 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:


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

  • Hollie Green
    Hollie Green ✭✭✭✭✭
    edited 08/11/23

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!