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

  • Christina09
    Christina09 ✭✭✭✭✭✭
    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

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

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Christina09
    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
    Christina09 ✭✭✭✭✭✭
    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.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!