Pulling a date from time stamp

Options
alexis.ray89371
alexis.ray89371 ✭✭✭✭✭
edited 08/05/22 in Formulas and Functions

Hi,

I am trying to set up a data shuttle configuration to auto load several worksheets I get a month. In the excel sheets there is a date/time stamp in a column.

I have loaded historical data into Smartsheet and now want to create a column that pulls the date out of the date/time stamp column. I used DATEONLY and referenced the time stamp field and it says "#INVALID DATA TYPE".

Does anyone know the correct way to do this formula? I need it to not only work in the sheet directly, but ensure that when I load a new sheet using data shuttle that it will work as well.

Thank you for any help!

Smartsheet: =DATEONLY([Date Occurred - Incident]@row)

The time stamp column is a text field because when I select date as a column type the time disappears and I don't want that to happen. The Date field is a date field.





Excel:





Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @alexis.ray89371

    Since you're mapping the content to a Text/Number field, the DateOnly function isn't able to identify a date from within your text.

    There is a way in Smartsheet to parse out the individual numbers and convert each section into a part of a date, however the formula is a little long and complicated. What I would suggest in this instance is to have a duplicate column in your source data, in the Excel sheet. Then you can map the duplicate column into a Date Column in Smartsheet - giving you one Text/Number column with the time, and one Date Column without the time.

    Let me know if that makes sense and will work for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!