# Calculate work days in formula, excluding weekends

Options
✭✭✭

I have three columns, Projected Hours to complete a task, Start Date and Finish Date. I have a Vlookup that populates the projected hours cell based on a product group identifier. Someone then enters a start date and i have a formula in the finish date cell that calculates the end date based on the projected hours. I would like this finish date cell to exclude weekends and non work days. Is there any way to do that with a formula? I know Smartsheet has built in features that accomplish this with Project Settings, unfortunately this did not produce our desired result in our workflow solution leading me to come up with a work around.

Any help would be appreciated.

Thank you,

Terry

Tags:

• ✭✭✭✭✭✭
Options

Check out the NETWORKDAY and NETWORKDAYS functions. Links included.

• ✭✭✭
Options

Thank you for your suggestion Paul.

Workday functions will not work as I am not looking to get a number of days between two date cells. I need an End Date cell to populate based on 1 date and an hours cell. I would like the End Date that is populated to take both of these columns into account and skip Saturday and Sunday.

If I assign a Work Center to a 16 hour task on 5/1/2020, I would like the End Date to Populate 5/4/2020 and skip the weekend.

Projected Hours are input automatically using a vlookup, Start Date is then manually input and the End Date is calculated based on the two.

Current Formula for End Date column>>>> =IFERROR(StartDate) + [Projected Hours]/ 8, "")

• ✭✭✭✭✭✭
Options

Sorry about that. I had my functions backwards. Check out the WORKDAY function. THAT is the one I originally meant to grab as it returns a date. My apologies on the mix up. Topping off the coffee now...

• ✭✭✭
Options

Appreciate the help Paul, because of your advice I have added a work_days column.

Formula>>> =IFERROR(ROUND([Projected Hours]/ 8, 0), "")

End Date is then using the WORKDAY function you mentioned to return my desired date. Exactly what I was looking to do, thank you Paul.

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!