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:Use a System Column to Automatically Add 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.
-
你好,我有de值表,但我不能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
In your sheet, create a third section that adds the results of the first two. It would have the same rows, but the formula for each row would add the cells of section 1 + section 2.<\/p>
Add a helper column called ShowInReport as a Check Box and check all the rows in the third section. <\/p>
Create a report and tell it to show you only Section 3 (where the ShowInReport is true.<\/p>
I hope that makes sense.<\/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":108864,"type":"question","name":"Is there a formula to show as %","excerpt":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % =\"We are at \" + [% closed rate]@row + \"…","snippet":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a…","categoryID":322,"dateInserted":"2023-08-11T17:22:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":144360,"lastUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":36,"score":null,"hot":3383558496,"url":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","format":"Rich","lastPost":{"discussionID":108864,"commentID":390301,"name":"Re: Is there a formula to show as %","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390301#Comment_390301","dateInserted":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+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-11T19:29:56+00:00","dateAnswered":"2023-08-11T17:49:58+00:00","acceptedAnswers":[{"commentID":390269,"body":"
Try this - =\"We are at \" + [% closed rate]@row * 100 + \"% closed rate on ticket status for the month of \"+[Month]@row.<\/p>"},{"commentID":390301,"body":"
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/comment/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/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":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"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}