Netdays Negative Return
Hi All!
I'm running into a quirk when using the netdays formula."Create Date" is a system generated column used for the start date, and "Resolution Date" is the end date.
When I have a record where the resolution date is the same as the startdate, I'm getting a -2 for the netdays between these values. Any idea what's going on?
=IF(ISBLANK([Resolution Date]@row), NETDAYS([email protected], TODAY()), NETDAYS([email protected], [Resolution Date]@row))
Best Answers
-
Alejandra EmployeeHi@cmondo,
I believe this is happening because the underlying dates and times in Smartsheet are all in the UTC time zone. Hypothetically speaking, if you're located in the Central Time zone, anything created after 6:00 PM would reflect the next day.
If needed, more information on this can be found here:https://help.smartsheet.com/articles/1108408-auto-numbering-rows#advanced
-
Alejandra Employee
@cmondowe typically communicate about new or improved features once they’ve been released, but I'd encourage you to submit aProduct Enhancement Requestto let our Product team know that you'd like to have this functionality in Smartsheet.
Answers
-
Alejandra Employee
Hi@cmondo,
I believe this is happening because the underlying dates and times in Smartsheet are all in the UTC time zone. Hypothetically speaking, if you're located in the Central Time zone, anything created after 6:00 PM would reflect the next day.
If needed, more information on this can be found here:https://help.smartsheet.com/articles/1108408-auto-numbering-rows#advanced
-
Chris Mondeau ✭✭✭✭✭✭
Thanks@Alejandra! Is there any development to adjust this per account/sheet in the future? This seems to be a common problem in a few other threads. Thank you!
-
Alejandra Employee
@cmondowe typically communicate about new or improved features once they’ve been released, but I'd encourage you to submit aProduct Enhancement Requestto let our Product team know that you'd like to have this functionality in Smartsheet.
Help Article Resources
Categories
Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?<\/p>
=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\"), \"Untrained\", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\")), \"In Progress\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"In Process\") >= 1, \"In Process\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Trained\") >= 1, \"Trained\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Proficient\") >= 1, \"Proficient\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Preceptor\") >= 1, \"Preceptor\")))))))<\/p>
Please let me know what we need to tweak<\/p>
Kelly<\/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":107653,"type":"question","name":"Sum all children rows under parent","excerpt":"I have my project worksheets organized by year, quarter, month, project, etc. Is there a simple formula to sum all children rows one indent in? For example, all months sum up to the quarters which sum up to the year? I only want them to sum at one indent in from the immediate parent row.","snippet":"I have my project worksheets organized by year, quarter, month, project, etc. Is there a simple formula to sum all children rows one indent in? For example, all months sum up to…","categoryID":322,"dateInserted":"2023-07-16T12:23:14+00:00","dateUpdated":"2023-07-17T08:25:09+00:00","dateLastComment":"2023-07-17T13:08:24+00:00","insertUserID":163321,"insertUser":{"userID":163321,"name":"HH_BE1894","title":"Brand Manager","url":"https:\/\/community.smartsheet.com\/profile\/HH_BE1894","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T13:40:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-17T13:46:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":34,"score":1,"hot":3379111598,"url":"https:\/\/community.smartsheet.com\/discussion\/107653\/sum-all-children-rows-under-parent","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107653\/sum-all-children-rows-under-parent","format":"Rich","lastPost":{"discussionID":107653,"commentID":385533,"name":"Re: Sum all children rows under parent","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385533#Comment_385533","dateInserted":"2023-07-17T13:08:24+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-17T13:46:08+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":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-17T11:09:14+00:00","dateAnswered":"2023-07-17T09:37:31+00:00","acceptedAnswers":[{"commentID":385514,"body":"
I hope you're well and safe!<\/p>
Try something like this for the helper column. (change the column names as needed)<\/p>
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)<\/p>
Be safe, and have a fantastic week!<\/p>
Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/9F0O6E6ZHU20\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>"},{"commentID":385520,"body":"
Thanks for your answer !<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">