Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
Overriding Non-Working Days
Most of our projects include M-F working dates, however at the end of each project there is often a conference or event which may take place over a weekend or other non-working day. I'd love to be able to override and enter tasks on weekends for these instances without turning the entire project into a 7-day work week project and accidentally having other tasks "pushed" onto a weekend by mistake. Is there a way to override?
Comments
-
Victor van R ✭✭
I think the only way is to make the default a 7 days working week and make the 'normal' weekends exception.
-
Do you mean set the working week to 7 days but make all weekends (except for the weekend of the event) a non-working/holiday/vacation status?
That might work. Thanks!
-
Victor van R ✭✭
Yes, that is what I mean. It is a bit basic workaround but it works.
-
If its only only a weekend here and there, you could do the following:
——创造一个新的任务(行)for Sat and Sun (each row only scheduled on one day)
-Create a row above the two weekend rows.
-Indent the two weekend rows under the new row
Now you can collapse the two weekend rows under the parent row to give the impression of a single row but still have your weekend task.
-
Crissy Nolen ✭✭
- Elapsed time (lead with an e to indicate duration in elapsed time e.g. e3d, e5h, e3.5d, etc). A task with a duration in elapsed time ignores non-working time. The task will start and end in the amount of time specified regardless of working day length and non-working days in the sheet.
-
Adding the Sat and Sun rows indented gives the impression in the gantt view but the days total is only 1d for a Fri-Sun range instead of 3d.
-
Thanks "Crissy Nolen"
I'd tried it and it is working fine
-
Brilliant, thank you!
-
Thanks for solving this for me -- just the guidance I needed!
-
Crissy, hello.
I have a date and duration calculating problem where a task of 6 days duration is behaving in an odd manner when I try to use your prefix 'e' solution to "override" Sat and Sun as non-work days within Project Settings.
Grid and Calendar Views both show 7 days Duration instead of the specified/required 6 days, and Gantt View shows just 5 days.
Any thoughts as to what I may be doing wrong?
Any help appreciated!
-
Zachw ✭✭✭
Thank you for this, works perfectly
Categories
<\/p>
This does the percentage:<\/p>
(TODAY() - Start@row) \/ (Finish@row - Start@row)<\/p>
<\/p>
This caps it at 100%:<\/p>
MIN(..............., 1)<\/p>
<\/p>
This keeps it from going negative for future tasks:<\/p>
MAX(..............., 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106871,"type":"question","name":"Parent Status based on Children Harvy ball status.","excerpt":"Dear team, I am trying to build a status update tracker, for teams to update each milestone. They are expected to update each milestone using the harvey ball status (0% \/25% \/50 % \/75% \/100%). I am trying to automate the status of parent based on children's Harvey ball status as below with logic as \" If the status of all…","categoryID":322,"dateInserted":"2023-06-25T03:09:21+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T02:39:36+00:00","insertUserID":162738,"insertUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162738,"lastUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3375410937,"url":"https:\/\/community.smartsheet.com\/discussion\/106871\/parent-status-based-on-children-harvy-ball-status","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106871\/parent-status-based-on-children-harvy-ball-status","format":"Rich","lastPost":{"discussionID":106871,"commentID":382196,"name":"Re: Parent Status based on Children Harvy ball status.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382196#Comment_382196","dateInserted":"2023-06-26T02:39:36+00:00","insertUserID":162738,"insertUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/81O3LR1GWG7D\/picture1.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Picture1.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T02:28:56+00:00","dateAnswered":"2023-06-25T04:29:41+00:00","acceptedAnswers":[{"commentID":382168,"body":"
With the symbols you are using, the options would typically be represented by the text entries [Empty, Quarter, Half, Three Quarter, Full]. In this case, this should work:<\/p>
=IF(COUNTIF(CHILDREN([BallCell]@row), \"Empty\") = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), \"Full\") = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>
If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:<\/p>
=IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>
In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":[]},{"discussionID":106869,"type":"question","name":"Formula to Assign Symbol based on a cell's value","excerpt":"This is a complicated one that I once got to work with RGY balls, but cannot figure out how to apply this to the Pain scale emojis. What I am trying to do: (Using the pain scale symbols) Have symbols automatically changed based on the value in the corresponding cell. I am already using a formula to assign a value based on…","categoryID":322,"dateInserted":"2023-06-24T20:02:20+00:00","dateUpdated":null,"dateLastComment":"2023-06-25T22:29:54+00:00","insertUserID":158092,"insertUser":{"userID":158092,"name":"MeganJF","url":"https:\/\/community.smartsheet.com\/profile\/MeganJF","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DgFow9e-1vo!YfMmmJOUTjo!UDpVBcB6ikY","dateLastActive":"2023-06-25T17:43:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":139601,"lastUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T04:50:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":31,"score":null,"hot":3375372134,"url":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106869,"commentID":382193,"name":"Re: Formula to Assign Symbol based on a cell's value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382193#Comment_382193","dateInserted":"2023-06-25T22:29:54+00:00","insertUserID":139601,"insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T04:50:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-25T17:10:13+00:00","dateAnswered":"2023-06-25T03:32:30+00:00","acceptedAnswers":[{"commentID":382165,"body":"
Hi @MeganJF<\/p>
I guess you forget to use AND( ).<\/p>
For example, <\/p>
- IF([Total Value to User Score]@row = <3.8, >4.7, \"Mild,<\/li><\/ul>
should be<\/p>
- IF(AND(<\/strong>[Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7)<\/strong>, \"Mild\",<\/li><\/ul>
However, the following would be more straightforward.<\/p>
- =IF([Total Value to User Score]@row >= 4.8, \"No Pain\", <\/li>
- IF([Total Value to User Score]@row >= 3.8, \"Mild\", <\/li>
- IF([Total Value to User Score]@row >= 2.8, \"Moderate\", <\/li>
- IF([Total Value to User Score]@row >= 1.8, \"Very Severe\", <\/li>
- IF([Total Value to User Score]@row < 1.8, \"Extreme\", \"//www.santa-greenland.com/community/discussion/2319/\")))))<\/li><\/ul>
- IF(AND(<\/strong>[Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7)<\/strong>, \"Mild\",<\/li><\/ul>