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":"
Hi @HH_BE1894<\/a> <\/p> I hope you're well and safe!<\/p> You can add a so-called helper column that indicates a level and then use that in the calculation.<\/p> Try something like this for the helper column. (change the column names as needed)<\/p> I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more.<\/p> =IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)<\/p> Would that 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>"}]}},"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":1},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107613,"type":"question","name":"Add pictures inside a cells which look like a form - Workapps","excerpt":"Hi everyone, I have just discover a thing in the Workapps. I made the same as all the other date : open my sheet (grid view) on my workapps. However, this time the sheet does'nt look likes a grid view but like card and we can add pictures inside a cells. You can see that in the picture that I add. If anydbody have a…","snippet":"Hi everyone, I have just discover a thing in the Workapps. I made the same as all the other date : open my sheet (grid view) on my workapps. However, this time the sheet does'nt…","categoryID":322,"dateInserted":"2023-07-14T10:56:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-17T10:23:13+00:00","insertUserID":151421,"insertUser":{"userID":151421,"name":"lea.s6","url":"https:\/\/community.smartsheet.com\/profile\/lea.s6","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!G69Wn8Gn53s!xLDXMWmx7Oc!BP_1d2Auq2F","dateLastActive":"2023-07-17T10:57:43+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-17T13:57:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":48,"score":null,"hot":3378925193,"url":"https:\/\/community.smartsheet.com\/discussion\/107613\/add-pictures-inside-a-cells-which-look-like-a-form-workapps","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107613\/add-pictures-inside-a-cells-which-look-like-a-form-workapps","format":"Rich","lastPost":{"discussionID":107613,"commentID":385521,"name":"Re: Add pictures inside a cells which look like a form - Workapps","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385521#Comment_385521","dateInserted":"2023-07-17T10:23:13+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-17T13:57:40+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/ZA10DMWH9B2G\/image-2023-07-14-11-46-59.jpeg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Image 2023-07-14 11-46-59.jpeg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-17T10:19:13+00:00","dateAnswered":"2023-07-17T10:08:37+00:00","acceptedAnswers":[{"commentID":385518,"body":"
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/9F0O6E6ZHU20\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
