Extracting the Time from the timestamp

Retail Experience

The question relates to extracting the Time from the timestamp when the "Column Type" is "Auto-Number/System" and the the "System-generated Column" is "Created (Date)"

your assistance is greatly appreciated

Mark Cronk

Hi @Retail Experience ,

To extract the time from a Created column try:

=SUBSTITUTE(Created@row, DATEONLY(Created@row), "")

To extract the date you would use:

=DATEONLY(Created@row)

Work?

Mark

Mark Cronk

Hi @Retail Experience ,

To extract the time from a Created column try:

=SUBSTITUTE(Created@row, DATEONLY(Created@row), "")

To extract the date you would use:

=DATEONLY(Created@row)

Work?

Mark

Retail Experience

Thank You

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark

Ella

Hi Mark @Mark Cronk , do you happen to have any solution for extracting the time from a regular date column? Thanks!

Mark Cronk

Hi Ella,

I don't going that's possible. A date cell doesn't record time. Auto columns create and modify have time stamps.

Mark

Andrée Starå

Hi @Ella

I hope you're well and safe!

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help? 

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Mark Cronk

Hi @Andrée Starå , Well done. Very creative. I've bookmarked it.

Mark

Ella

Hi @Andrée Starå ,

unless I am failing somewhere I don't think your solution works for me. Created date/time in the copy sheet is still the one stamped when the row was created in the original sheet. I need to isolate the time of the completion date which would be entered in a different column in the original sheet and would be different from the created date. Technically the date could be the same, but the time would be different.

Thanks!

FredeGoulet

Hi guys, I did use the solution proposed by @Mark Cronk but it doesn't seems to work at each time... Sometimes, the hour doesn't extract properly as you can see (full date format). And look, when this happen, the date (middle column) is not right either, it's the next day.

I'm kind of confused about the right solution to apply to only extract the hour and the right date form the creation stamp.

Can you help me?

SOLUTION USED:

To extract the time from a Created column try:

=SUBSTITUTE(Created@row, DATEONLY(Created@row), "")

To extract the date you would use:

=DATEONLY(Created@row)

Genevieve P.

Hi @FredeGoulet

It looks like you may be in a different timezone than what the System Date records dates/times in. System Date columns store dates in UTC, see: Time zones and system columns

To extract the time from a Created column try using the RIGHT function instead:

=RIGHT(Created@row, 8)

I would also recommend using a Record a Date workflow instead of the Date column, which will record the date in your current timezone. See: Set the current date with the Record a Date action

Cheers,

Genevieve