How to have a Date and Time format for Date Column Properties?

Genevieve P.

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

Guilherme Jaquer

https://community.smartsheet.com/discussion/comment/275158#Comment_275158

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 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.

JeremiahHorstick

@Guilherme Jaquer I did this a year ago.

If you simply use the "Get Sheet" method the values are there.

SJ Sellers

@JeremiahHorstick

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

SJ Sellers 

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

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.

JeremiahHorstick

@SJ Sellers Please scrub it and publish a viewer copy that can be copied and edited.

Guilherme Jaquer

https://community.smartsheet.com/discussion/comment/328296#Comment_328296

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

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

Guilherme Jaquer

https://community.smartsheet.com/discussion/comment/328985#Comment_328985

Neither via API? It's really SAD, i just submitted this request lets wait.

Curaga

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. 😥

JeremiahHorstick

It's been my experience that ISO datetimes can be accessed and modified via the API.

Steve Bancroft

https://community.smartsheet.com/discussion/comment/369629#Comment_369629

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?

Bernardo Ticom

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.