Pulling a date from time stamp

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

  • 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!