Extract date and Time


Is it possible to extract a date/ time from an imported excel file? It is currently reported as a string 2020112717161730.

Thanks in advance for the assist!


Best Answer


  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @Katye Reed If all you need is the date, it is easy to stitch it together from that data field:

    =DATE((VALUE(LEFT([Column41]@row, 4))), (VALUE((RIGHT(LEFT([Column41]@row, 6), 2)))), (VALUE((RIGHT(LEFT([Column41]@row, 8), 2)))))

    ... where your string is in Column41.

    This only gets you date though. I do not believe there is any way to create and hold the time (speaking practically, not existentially).

    If you want to display the data formatted as time, you could do this:

    =VALUE(LEFT(RIGHT([Column41]@row, 8), 4)) + ":" + VALUE(LEFT(RIGHT([Column41]@row, 4), 2)) + ":" + VALUE(RIGHT([Column41]@row, 2))

    ... should give you this: 2020/11/27 1716:17:30.



  • Katye Reed
    Katye Reed ✭✭✭✭✭
    Answer ✓

    @DMurphy Thanks so much. The first formula worked perfectly. The 2nd gave me a weird results; 1716:17:30

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!