Days Off

I need to show a list of employees available to work on a given date. I don't want to show employees available if they are on their days off.

Is there a way to look at today's date and determine if it has fallen on an employees day off if I provide the weekdays they are off?

Thank you.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Ronald Anderson

    Do you mean weekdays as in their shift schedule (ex. Monday, Wednesday, Friday) or days off as in specific holiday dates they have booked off?

    There are some templates in the Solution Center built for tracking employee time off, see: Team Vacation Planner or Staff Plan Management template set.

    If you just need to tell what day of the week they're working and if they're available on the selected date, you could set up a reference sheet that shows everyone's scheduled days, like so:


    Then in the sheet where you're adding dates, you can use a Nested IF formula in a helper column to identify if the date you've added is Monday, or Tuesday etc, returning the same value you have in the chart above:

    =IF(WEEKDAY(Date@row) = 1, "Su", IF(WEEKDAY(Date@row) = 2, "M", IF(WEEKDAY(Date@row) = 3, "T", IF(WEEKDAY(Date@row) = 4, "W", IF(WEEKDAY(Date@row) = 5, "Th", IF(WEEKDAY(Date@row) = 6, "F", IF(WEEKDAY(Date@row) = 7, "Sa")))))))

    Then you can use whatever has been populated as the value to look for in the chart, and return all the Employee names that are available on that day of the week:

    =JOIN(COLLECT({Employee Name}, {Work Days}, HAS(@cell, WeekDay@row)), ", ")

    Is this what you were looking to do?

    Cheers,

    Genevieve

  • Ronald Anderson
    Ronald Anderson ✭✭✭✭

    Thank you for your suggestions. I don't know the solution yet, but this gives me some ideas to investigate.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!