Working Days
Greetings,
I have the start day and the duration. Is it possible to calculate the end date using these two entitites such that only working days are included (only monday through friday and not the weekends)?
Thank you
Comments
-
Paul Newcome ✭✭✭✭✭✭I would take a look at the WORKDAY function.
WORKDAY()
WORKDAY(date, num_days, [holidays])
Used in a Date column to return a date from a specified number of working days. Optionally excludes holidays outlined in a range.
10xViz
-
Mike Wilday ✭✭✭✭✭✭Hmmm. You might also try turning on dependencies. It will set up a start time and a finish time column if you don't already have one and calculate the finish time for you. When you edit the start date or the duration, the end date will calculate for you automatically.
- https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors
- https://www.techrepublic.com/article/how-to-set-up-projects-in-smartsheet/
That might be a better way of doing it rather than using custom formulas.
-
Mike Wilday ✭✭✭✭✭✭
Another added benefit of turning on dependencies is that you can also make the end of one task determine the beginning of the next one.
-
Thank you very much.
It worked!
-
Paul Newcome ✭✭✭✭✭✭
Happy to help!
I would also suggest looking into dependencies as Mike suggested below. I don't use them for various reasons, so I never think about them as a solution.
10xViz
-
Bryan Mac ✭✭
Can this be done without using a function?
Categories
The answer is YES. You can use \"Send as Attachment\" feature in the File menu of your sheet to setup the schedule for your need. You should be a licensed user to use this feature as well (I remembered that). <\/p>
Hope this works for you.<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/G1SDKXPQ7I1D\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n<\/img><\/a>\n <\/div>\n<\/div>\n<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":268,"urlcode":"Attachments","name":"Attachments"},{"tagID":300,"urlcode":"Email Notifications","name":"Email Notifications"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"}]},{"discussionID":108180,"type":"question","name":"How can I make an approver's view on the message notification field mandatory?","excerpt":"Making it mandatory will not allow approver to approve or decline without acknowledgement. Example creating a column that would say: \"You acknowledged that accounting details have been reviewed?\" Then enters initials. This has to be done prior to clicking approved or decline.","snippet":"Making it mandatory will not allow approver to approve or decline without acknowledgement. Example creating a column that would say: \"You acknowledged that accounting details have…","categoryID":321,"dateInserted":"2023-07-27T14:08:57+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T18:44:32+00:00","insertUserID":157632,"insertUser":{"userID":157632,"name":"Shirley Romero","url":"https:\/\/community.smartsheet.com\/profile\/Shirley%20Romero","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/6RM6Q0PW4X07\/nLP5DK4J5PI9I.jpg","dateLastActive":"2023-07-27T15:36:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T04:02:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3380952209,"url":"https:\/\/community.smartsheet.com\/discussion\/108180\/how-can-i-make-an-approvers-view-on-the-message-notification-field-mandatory","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108180\/how-can-i-make-an-approvers-view-on-the-message-notification-field-mandatory","format":"Rich","tagIDs":[300],"lastPost":{"discussionID":108180,"commentID":387613,"name":"Re: How can I make an approver's view on the message notification field mandatory?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387613#Comment_387613","dateInserted":"2023-07-27T18:44:32+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T04:02:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T15:38:54+00:00","dateAnswered":"2023-07-27T14:43:11+00:00","acceptedAnswers":[{"commentID":387545,"body":"
This cannot be set up through an Approval Automation. At his point you have a few options (each of which has its pros and cons).<\/p>
<\/p>
You can just include a blurb that says that they are acknowledging by submitting within the Approval automation.<\/p>
<\/p>
You can set up an update request automation as a reminder that they need to enter their initials if they submit but didn't the first time.<\/p>
<\/p>
The last option I can think of that would truly make it required is a bit complex but possible. The first step is to create an \"approval sheet\". This sheet will have the same fields in it that you need to send in the approval request and a form created to match.<\/p>
<\/p>
You would then take the base form URL and drop it into a formula on the original sheet that allows you to basically generate a custom form on each row that will have the row data prefilled when someone clicks on the URL in that row. Since you can make form fields required, this will give you the ability to require their acknowledgement prior to them submitting their approval.<\/p>
<\/p>
Send this custom form URL through an alert and instruct the users to click the link to open the approval form.<\/p>
<\/p>
The form entry would populate the \"approval sheet\", but you can use formulas with cross sheet references to pull the appropriate data back over to your original sheet.<\/p>
<\/p>
If that last option sounds like something you would be interested in, fell free to let me know, and I will try to pull together a few links to help you get started building the formula.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":300,"urlcode":"Email Notifications","name":"Email Notifications"}]},{"discussionID":108166,"type":"question","name":"Synchronization of Smart sheet and Outlook Calendar","excerpt":"I am trying to synchronize an appointment list created with Smart sheet with Outlook Calendar. I was able to get the date to reflect in Outlook Calendar, but is there any way to get the time and the content of the appointments on the Smart sheet to reflect in Outlook Calendar?","snippet":"I am trying to synchronize an appointment list created with Smart sheet with Outlook Calendar. I was able to get the date to reflect in Outlook Calendar, but is there any way to…","categoryID":321,"dateInserted":"2023-07-27T07:37:41+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T00:35:35+00:00","insertUserID":144305,"insertUser":{"userID":144305,"name":"Kenji","url":"https:\/\/community.smartsheet.com\/profile\/Kenji","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!A2lcx_I5Ojw!m8kqn54NZSs!obOvPtLS1JT","dateLastActive":"2023-07-28T01:52:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-07-28T09:15:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3380949796,"url":"https:\/\/community.smartsheet.com\/discussion\/108166\/synchronization-of-smart-sheet-and-outlook-calendar","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108166\/synchronization-of-smart-sheet-and-outlook-calendar","format":"Rich","lastPost":{"discussionID":108166,"commentID":387721,"name":"Re: Synchronization of Smart sheet and Outlook Calendar","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387721#Comment_387721","dateInserted":"2023-07-28T00:35:35+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-07-28T09:15:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/9CM5AMI8BDNA\/02.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"02.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T21:02:03+00:00","dateAnswered":"2023-07-27T08:24:09+00:00","acceptedAnswers":[{"commentID":387453,"body":"
Hi @Kenji<\/a> <\/p> I hope you're well and safe!<\/p> Unfortunately, it's not possible now, but it's an excellent idea!<\/p> Please submit this as a Product Feedback or Idea <\/strong>(If it hasn't been added already)<\/em><\/strong> when you have a moment.<\/strong><\/a><\/p> Here's a possible workaround or workarounds <\/strong><\/p> Would any of those options work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"},{"commentID":387721,"body":"
