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

Tags:

## Answers

• ✭✭✭✭✭✭
edited 06/10/24
Options

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.

• Options

what column should i put that formula?

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

• Options

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.

• ✭✭✭✭✭✭
edited 06/11/24
Options

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.

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

• Options

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!