Calculate work days in formula, excluding weekends

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

Find more posts tagged with

Formulas

functionality

Accepted answers

All comments

Paul Newcome

Check out the NETWORKDAY and NETWORKDAYS functions. Links included.

TJohnson

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, "")

Paul Newcome

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

TJohnson

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.

Paul Newcome

Happy to help! 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.