How to have a Date and Time format for Date Column Properties?
Answers
-
Genevieve P. Employee AdminThe "Last Updated" column would need to be a System Date Column, not a regular date column. See:使用系统自动添加列Information to a Row
-
Hello Jeremiah, how are you?
I thought it super interesting and amazing to be able to put the time (minutes and hours) of the actions via API, do you have any smart documents that can help me create this API? It would help a lot.
Thanks and best regards.
-
JeremiahHorstick ✭✭✭✭✭✭@SJ SellersI tried this method. Pretty cool.
I played with the Project Settings.
Setting the length of Days to 24 hrs causes the date time to default to 12AM.
Back to what you are saying. This allows us to set the start/end time only via duration values, but not the start or end date column.
-
JeremiahHorstick ✭✭✭✭✭✭
@Guilherme JaquerI did this a year ago.
If you simply use the"Get Sheet"method the values are there.
-
SJ Sellers ✭✭✭
For a technology cutover plan which requires time level precision, I used this method to manage a 1,800 line plan. It worked - but I would not say it worked "well".
For tasks that had a defined start time, we created what we called "anchor rows" with fixed durations. The task that required a defined start time would be dependent on that "anchor row" so that it would reflect the correct start time. So for example, if a task needed to start at 1 PM, it would be dependent on a task with a 13h duration, assuming a 24 h day.
We inserted two columns with formulas to reflect the Start and End Times. Here's what that looked like for reference:
Start (and End) time formulas: =RIGHT([Start Date]@row + " ", LEN([Start Date]@row + " ") - 9)
You can see in my screenshot that "behind the scenes", SmartSheet is managing the times as you would expect. I am not manipulating the times at all - just exposing them exactly as SmartSheet is calculating them in the Start and End Date columns of a Project Enabled sheet.
Note this workaround approach does not rely on a 24 h day - we were a global team working around the clock which required all kinds of other considerations I'll save for another post. Here is an example with varying durations using a standard 8 Hour day, for those who are curious:
Funny side note: Smartsheet assumes an hour for lunch. :-) notice the First Task ends at 2 PM, and not 1 PM, which is 6h rather than 5h after the start.
This anchor date workaround approach required checking and rechecking and triple-rechecking the times throughout the planning and execution process. We needed to ensure the workarounds did not have unexpected results. It also meant that the process was not as collaborative as it could have been because all plan updates needed to go through me - this is definitely an "advanced technique" not meant for casual users. Being able to directly enter start times would be a game-changer.
I also noticed this sheet was pretty slow - it seemed the formulas caused a bit of a drag.
-
JeremiahHorstick ✭✭✭✭✭✭
Interesting. I'll take a closer look. Will you share a copy of the sheet with me so that I can play with it?
-J
-
SJ Sellers ✭✭✭
Unfortunately, I cannot share this sheet.The sheet has ~100 contact names from 4 different companies and other personally identifying details that would be difficult to scrub.
Hopefully the screenshots above give you ideas to get start and I am happy to answer specific questions.
-
JeremiahHorstick ✭✭✭✭✭✭
@SJ SellersPlease scrub it and publish a viewer copy that can be copied and edited.
-
Hello, I got de values from the sheet, but i cant change the time of te start column, i can change duration and date for example, but start time i can't. Have you ever been able to change this field?
Thanks.
-
SJ Sellers ✭✭✭
The "Time" portion of the field CANNOT be directly changed. It would be great if SmartSheet would allow the field to be changed - please join me and others by submitting an enhancement request.https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?Origin=help
-
Neither via API? It's really SAD, i just submitted this request lets wait.
-
Curaga ✭
Still not coming to Smartsheet?It's2023now. This is a crucial business requirement, though.
We just wanted a, Smartsheet readable, ISO 8601 date (yyyy-MM-dd HH:mm:ss) and not separating date and time per column or any other workaround. It's natively working in Excel. We love Smartsheet because of thoseautomated workflowsand that's it.
-
JeremiahHorstick ✭✭✭✭✭✭
It's been my experience that ISO datetimes can be accessed and modified via the API.
-
SandraMS ✭✭ -
I totally agree. Being able to insert the time & date into a single cell and calculate the duration between a start and end should be integrated. I understand there was a feature request submitted, did anything ever come of that?
Help Article Resources
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+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\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"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-08-26T00:32:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+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-08-26T00:32:09+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-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
![https:\/\/us.v-cdn.net\/6031209\/uploads\/0XVZHLCQUR06\/image.png<\/a><\/p>Time Data exposed by API:<\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/X04JQPGSE9P5\/image.png<\/a><\/p>We could make the time data readable and\/or put it in a format that hours\/minutes\/seconds can be accessed with a formula. <\/p>😎<\/span><\/p>","bodyRaw":"[{\"insert\":\"Interestingly I have seen that date type columns do indeed contain date\\\/time data however it can only be exposed via the API. \\nWe could take the dates from a critical path with minute duration values and present it in a text column if you want.\\n\\nTime Test Sheet for Demonstration:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/0XVZHLCQUR06\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/0XVZHLCQUR06\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nTime Data exposed by API:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/X04JQPGSE9P5\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/X04JQPGSE9P5\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nWe could make the time data readable and\\\/or put it in a format that hours\\\/minutes\\\/seconds can be accessed with a formula. \\n\"},{\"insert\":{\"emoji\":{\"emojiChar\":\"😎\"}}},{\"insert\":\"\\n\\n\\n\"}]","format":"rich","dateInserted":"2021-01-14T02:06:36+00:00","insertUser":{"userID":21126,"name":"JeremiahHorstick","url":"https:\/\/community.smartsheet.com\/profile\/JeremiahHorstick","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-07\/5b\/45\/n5b45eb0c1a48afae956282248c8ad587.jpg","dateLastActive":"2023-08-11T02:52:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/275158#Comment_275158","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/275158#Comment_275158](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/0XVZHLCQUR06\/image.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/X04JQPGSE9P5\/image.png<\/a><\/p>We could make the time data readable and\/or put it in a format that hours\/minutes\/seconds can be accessed with a formula. <\/p>😎<\/span><\/p>","bodyRaw":"[{\"insert\":\"Interestingly I have seen that date type columns do indeed contain date\\\/time data however it can only be exposed via the API. \\nWe could take the dates from a critical path with minute duration values and present it in a text column if you want.\\n\\nTime Test Sheet for Demonstration:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/0XVZHLCQUR06\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/0XVZHLCQUR06\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nTime Data exposed by API:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/X04JQPGSE9P5\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/X04JQPGSE9P5\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nWe could make the time data readable and\\\/or put it in a format that hours\\\/minutes\\\/seconds can be accessed with a formula. \\n\"},{\"insert\":{\"emoji\":{\"emojiChar\":\"😎\"}}},{\"insert\":\"\\n\\n\\n\"}]","format":"rich","dateInserted":"2021-01-14T02:06:36+00:00","insertUser":{"userID":21126,"name":"JeremiahHorstick","url":"https:\/\/community.smartsheet.com\/profile\/JeremiahHorstick","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-07\/5b\/45\/n5b45eb0c1a48afae956282248c8ad587.jpg","dateLastActive":"2023-08-11T02:52:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/275158#Comment_275158","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/275158#Comment_275158](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/X04JQPGSE9P5\/image.png\"){kind=link}