Need help with a formula

I need to be able to track when an employee needs a certificate of disability. A COD is required on the 4th sick occurrence recorded. An occurrence is defined as either 1 day OR 1 set of days that are consecutive. For example if I call off Mon-Wed, that is one occurrence. But if I call off Monday, come back Tuesday, and call off Wednesday, that would be two occurrences.

Thanks for any help!

Best Answer


  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Tim Copeland

    I suppose you have a sheet that registers all occurences with starting date, end date, and name of the person right?

    And another one where you want to do the automation and calculation?

  • I can do it that way if I have to but I have one sheet where the supervisor records the sick calloff.  Once they record that, I was going to set automation up to move the row to my COD record sheet and it would place that row at the bottom of the COD sheet.  It would have the employees name, a date column with the date they called off entered by the supervisor, and a time they called off in another column.  I would just keep the calculations at the top of that sheet unless that won't work.

  • Thanks for your help I think this will work!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!