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

Answers

  • Jason Albrecht
    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...

  • Christina.R
    Christina.R ✭✭✭✭✭✭

    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}, >[email protected], {End Date}, <[email protected], {employee}, CONTAINS("Bill", @cell)) + COUNTIFS({Start Date}, >[email protected], {End Date}, <[email protected], {employee}, CONTAINS("nancy", @cell)) + COUNTIFS({Start Date}, >[email protected], {End Date}, <[email protected], {employee}, CONTAINS("tyler", @cell)) + COUNTIFS({Start Date}, >[email protected], {End Date}, <[email protected], {employee}, CONTAINS("sarah", @cell))

    it is not counting it correctly, i'm not sure what i did wrong in here. Any idea why?

  • Christina.R
    Christina.R ✭✭✭✭✭✭
    Answer ✓

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

    =COUNTIFS({Start Date}, <[email protected], {End Date}, >[email protected], {employee}, CONTAINS("Bill", @cell)) + COUNTIFS({Start Date}, <[email protected], {End Date}, >[email protected], {employee}, CONTAINS("nancy", @cell)) + COUNTIFS({Start Date}, <[email protected], {End Date}, >[email protected], {employee}, CONTAINS("tyler", @cell)) + COUNTIFS({Start Date}, <[email protected], {End Date}, >[email protected], {employee}, CONTAINS("sarah", @cell))

    This works now.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭

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

  • Hi Christina,

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