Due Dates that exclude weekends and Holidays

Joely Kremer
edited 12/09/19 in Smartsheet Basics

I need a formula that will return a date that does not include weekends or holidays. 

For example:

Notification Date 1/18/19

Due Date (formula that counts 20 business days from the notification date and excludes holidays). It should return 2/16/19 because MLK day is on Monday)

Comments

  • Brian W
    Brian W ✭✭

    You can use:

    =WORKDAY([Notification Date]@row, 20, Holidays:Holidays)

    But you have to create a Holiday column and manually fill it with holidays.

    In this case it is returning 2/18 because 2/16 is a Saturday.

    Screen Shot 2019-01-18 at 4.25.52 PM.png

  • Detrie Zacharias
    Detrie Zacharias ✭✭✭✭✭

    This is great thanks for posting.

    I need to take it a step further but I cant figure out the syntax

    I have two supporting sheets to manage SLAs and Holidays

    1. SLA -  Two columns. Task Type and Days
    2.  Holidays - Two Columns. Name and Date

    Created is a timestamp

    I need to populate Due Date with this logic

    Due Date = Created (date only) + SLA (days column) account for workday and holiday (date column)

    This is the only part I can get to work so far

    =DATEONLY(Created1) + SLA1

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Stavros,

    Did you get it working or do you still need help?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.