How to exclude weekends while using formulas

brandon Calvert

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.

Mark.poole

@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])

brandon Calvert

what column should i put that formula?

Mark.poole

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

brandon Calvert

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

@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)

Mark.poole

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

brandon Calvert

Mark, thank you very much that worked!