How to have a Date and Time format for Date Column Properties?
Answers
-
Hi @zcornute
The "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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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.
-
@SJ Sellers I 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.
-
@Guilherme Jaquer I did this a year ago.
If you simply use the "Get Sheet" method the values are there.
-
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.
-
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
-
Hi @JeremiahHorstick -
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.
-
@SJ Sellers Please 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.
-
Hi @Guilherme Jaquer -
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.
-
Still not coming to Smartsheet? It's 2023 now. 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 those automated workflows and that's it. 😥
-
It's been my experience that ISO datetimes can be accessed and modified via the API.
-
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?
-
I'm struggling to create a Clock punch for my team because there is no time column.
The idea is to have a form where the person select its name and if they are clocking in or out, and the rest should be automatic, without the chance of the person change the time manually.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!