Data populated from a Data Shuttle is not recognized as a date, is there a way to fix this?

HEMac

I have a report that is being pulled and updated on a regular basis from a separate database. In order to bring that data into a sheet in smartsheet I have set-up a Data Shuttle. I am having an issue where one of the columns in the original excel which is meant to be formatted as a date also includes the time and timezone in the cell (see image with example of cells). Unfortunately I cannot control the format this data is pulled so this will always be in that format in he data pulls from the database. The smartsheet does not seem to read this as a date because of it. When creating the Data Shuttle the Mapping only allows me to select Auto as the Column type, I don't know if perhaps that is contributing to the issue as well. I am looking to create a report which filters based on date, but since it doesn't recognize it as a date the filter isn't working.

Any ideas?

Gia Thinh

Hi HEMac,

I got this idea :

1. You should create a "Modified Date" column in your sheet then map it with "Modified Date" column of the Excel file for uploading in Data Shuttle.
2. In the "Date of Page Modification" column, you can use formulas to get only the date text from the "Modified Date" column

Hope it helps.

Gia Thinh

Hi HEMac,

I got this idea :

1. You should create a "Modified Date" column in your sheet then map it with "Modified Date" column of the Excel file for uploading in Data Shuttle.
2. In the "Date of Page Modification" column, you can use formulas to get only the date text from the "Modified Date" column

Hope it helps.

HEMac

Thank you Gia

TomG

Hi,

I have hit this same issue and had already applied the helper column approach to separate out just the date portion of my data after importing with Data Shuttle. In my case, the problem appears to be that my data for month and day do not include preceding zeros when the value is less than 10. For example, January 5th, 2023 is represented as 1/5/2023 in my data, not 01/05/2023. I cannot sort properly using a data format without the preceding zeros. I use a helper column to load the data with Data Shuttle, then a column formula to derive the date which is what the sheet user sees. Both columns are formatted as date columns.

I am opening up a support ticket and will share what results I get from the support team.