Team Vacation Planner - automation to notify if too many people are taking the same day off
![Christina09](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hi there!
Is there any way smartsheet can set up an automation that if there are too many people taking the same day off it'll send an alert to me?
I have a start date and end date column
I also have another helper sheet with each day (excluding holiday, Saturday and Sunday) in each row and different departments as column. Let's say if on 7/21, IT department has 3 people off, it will alert me. 11/7, HR department has 5 people off and it'll alert me....etc.
I'm open to any idea and feel free to brainstorm together :)
Thanks!
Best Answer
-
Figured it out, just silly me having the wrong <>
=COUNTIFS({Start Date}, <=Weekdate@row, {End Date}, >=Weekdate@row, {employee}, CONTAINS("Bill", @cell)) + COUNTIFS({Start Date}, <=Weekdate@row, {End Date}, >=Weekdate@row, {employee}, CONTAINS("nancy", @cell)) + COUNTIFS({Start Date}, <=Weekdate@row, {End Date}, >=Weekdate@row, {employee}, CONTAINS("tyler", @cell)) + COUNTIFS({Start Date}, <=Weekdate@row, {End Date}, >=Weekdate@row, {employee}, CONTAINS("sarah", @cell))
This works now.
Answers
-
Did someone say brainstorm?
Ok, well, here's one suggestion...
An automation alert in the helper sheet that looks for a certain number in a certain colum (e.g. 3 or 4 in IT)
Limitations include:
- not dynamic (i.e. as more IT employees come on board, it'll be a manual edit of the automation)
- not identifying who has the time off (i.e. a manual review of the PTO sheet)
Advantages include:
- imediate set-up without much fuss
Hope this helps start the brainstorm...
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
That's what I was thinking too. But i have trouble getting a formula to count #o of people taking the day of, see below example of the IT department formula i have
=COUNTIFS({Start Date}, >=Weekdate@row, {End Date}, <=Weekdate@row, {employee}, CONTAINS("Bill", @cell)) + COUNTIFS({Start Date}, >=Weekdate@row, {End Date}, <=Weekdate@row, {employee}, CONTAINS("nancy", @cell)) + COUNTIFS({Start Date}, >=Weekdate@row, {End Date}, <=Weekdate@row, {employee}, CONTAINS("tyler", @cell)) + COUNTIFS({Start Date}, >=Weekdate@row, {End Date}, <=Weekdate@row, {employee}, CONTAINS("sarah", @cell))
it is not counting it correctly, i'm not sure what i did wrong in here. Any idea why?
-
Figured it out, just silly me having the wrong <>
=COUNTIFS({Start Date}, <=Weekdate@row, {End Date}, >=Weekdate@row, {employee}, CONTAINS("Bill", @cell)) + COUNTIFS({Start Date}, <=Weekdate@row, {End Date}, >=Weekdate@row, {employee}, CONTAINS("nancy", @cell)) + COUNTIFS({Start Date}, <=Weekdate@row, {End Date}, >=Weekdate@row, {employee}, CONTAINS("tyler", @cell)) + COUNTIFS({Start Date}, <=Weekdate@row, {End Date}, >=Weekdate@row, {employee}, CONTAINS("sarah", @cell))
This works now.
-
Glad it's all sorted. Have a great week.
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Hi Christina,
I'm looking to do the same thing with my team. Are willing to share the sheet?
Help Article Resources
Categories
Check out the Formula Handbook template!