How to extract time from Date created auto column?

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



    Ipshita Mukherjee

  • 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! :)

  • Have you tried this formula?

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!