Pulling a date from time stamp

alexis.ray89371

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:

Genevieve P.

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