Need help with a formula

Tim Copeland

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!

David Joyeuse

OK, seems to be what I had in mind, or close to. Don't think you really need the automation to make it works though.

In your rows at the top of your COD sheet, you need a column that I'd called COD occurence.

With this formula:

=COUNTIF({Employee Name 1st Sheet}, [Employee Name]@row)

It won't really matter if they called off for 1 day or more. It just need to appear if I understood you right.

If not, could you provide a screenshot or two with dummy data for better comprehension?

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?

Tim Copeland

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.

David Joyeuse

OK, seems to be what I had in mind, or close to. Don't think you really need the automation to make it works though.

In your rows at the top of your COD sheet, you need a column that I'd called COD occurence.

With this formula:

=COUNTIF({Employee Name 1st Sheet}, [Employee Name]@row)

It won't really matter if they called off for 1 day or more. It just need to appear if I understood you right.

If not, could you provide a screenshot or two with dummy data for better comprehension?

Tim Copeland

Thanks for your help I think this will work!