Team Vacation Planner - automation to notify if too many people are taking the same day off
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!