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!

Find more posts tagged with

Templates

Formulas

Automations

Accepted answers

Christina09

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.

All comments

Jason Albrecht

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...

Christina09

Hi @Jason Albrecht

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?

Christina09

Figured it out, just silly me having the wrong <>

This works now.

Jason Albrecht

Glad it's all sorted. Have a great week.

Dio Fernandez

Hi Christina,

I'm looking to do the same thing with my team. Are willing to share the sheet?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.