Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
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
Answers
-
Check out the NETWORKDAY and NETWORKDAYS functions. Links included.
-
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, "")
-
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...
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
