How to extract time from Date created auto column?

Colemont

Hi all, I want to extract only time from Date created auto column (in format HH:mm:ss)

I've tried VALUE(RIGHT(Created@row, 8)), but it seems i can only take two first numbers and ":" is blocking the formula, and i get unparsable if i go any higher than VALUE(RIGHT(Created@row, 2)).

What would be the correct approach to achieve this?

Ipshita

Hi @Colemont ,

Unfortunately, there is no easy workaround for what you are trying to achieve. Although there is the DATEONLY formula to extract the date from your auto-generated column, there is none to extract the time.

You'll need to insert a helper column in your current working sheet and a separate sheet for VLOOKUP (you can also use INDEX/MATCH formula if you dig in further).

So use the automation template to copy/move rows to another sheet, in this case your VLOOKUP sheet. This would be your date/time column (that was auto-created) that you copy over to the VLOOKUP sheet.

Now, using either the VLOOKUP or the INDEX/MATCH formula you can bring it back to the main sheet in your helper column (the one you create for bringing the time value).

I know this is a longer method but hopefully this helps. Please submit your excellent idea to have a calculation to separate time from a date column in Smartsheet portal.

Cheers!

Ipshita

Colemont

Hi and thank you for your answer. Unfortunately, we are not able to use helper columns anymore on our data sheets. We have 50+ columns already with 20 Helper columns of these. Many sheets are already reaching cell limits,. It would create even more frustration for other index/vlookups to etc. There are limits in these, so i'm looking for another solution. Thank you! :)

Sabrina Torok

Have you tried this formula?

=TIME(RIGHT([Created]@row, 8))

https://help.smartsheet.com/function/time