How to exclude weekends while using formulas

Options

I am very new to smartsheets and have just been playing with it and need guidance. I am trying to keep it as basic as possible. Essentially we are trying to backwards plan from our "due date" of when to begin our parts. i have it where you manually plug in a "shop time" and manually plug in a "outside lead time" have a formula to add the 2 together to get my "total lead time". You can then see the formula i have to get a start by date but am having trouble getting it to exclude weekends. I have read you cant have formulas and decencies so not sure what i need to do.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/10/24
    Options

    @brandon Calvert

    see how the networkday function works for you.

    =NETWORKDAY([START BY DATE]@row,[DUE DATE]@row)

    This function returns the number of working days between 2 dates. You could also add holidays into the function.

    =NETWORKDAY([START BY DATE]@row,[DUE DATE]@row,[Holiday Date])

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • what column should i put that formula?

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    Which ever column you want to show the the number of days in

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • I am ideally wanting the start by date to reflect when we should start not including weekends based off the "total lead time" amount. Rather than showing a number.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/11/24
    Options

    @brandon Calvert

    is the total lead time a duration column? Or are you adding in the d manually?. If it is a text column it is reading is the value as a text value thus you would have to remove the d. If you have it as a duration column you do not have to add the d as it is automatic. how ever you would want to create a helper row to create a numeric value.

    =WORKDAY([Due Date]@row, -Helper@row)

    This would go in your start date column

    See The screen shot.

    The workday function also has an optional entry for holiday dates

    =Workday(Date, Value, holiday)

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/11/24
    Options

    Strangly the first time I was building this formula it needed the helper column. Now it does not. so you can simply do

    =WORKDAY([Due Date]@row, -[Total Lead Time]@row)

    in the start date column.

    Just be sure that Total Lead time is a duration column, Start date and due date are both date columns

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark, thank you very much that worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!