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
-
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?
Answers
-
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.
-
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?
-
Thanks for your help I think this will work!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!