Data Shuttle

andrew.taylor37161

Hi all

I need help with Data Shuttle.

I use it a lot, however I have encountered an issue that I cannot resolve.

We take data from another platform and export it via csv. files. We use Datashuttle to collect this data and put it into Smartsheet to use in our reports and dashboards.

The issue that I have encountered is with the date format from the csv file. It converts it to US date format during the Data Shuttle process. This causes massive changes to items that have an end date (contracts etc), for example, a contract ending 3rd December, becomes ending on 12th March.

It does not do this in all cells, just the cells that have a date with a zero omitted in the first day. Example: 03/10/2023 in the program platform is captured in the excel sheet as 3/10/2023 (dd/mm/yyyy format) and this appears in the Smartsheet as 10/03/23.

Items listed as 30/09/2023 in the excel sheet appear in Smartsheet as 30/10/23

The excel (csv.) is in Aus date format.

Chrome is in Aus format.

My personal settings on Smartsheet are in Aus format.

The data shuttle process does not permit me to change the "column type" from "Auto" and if I change the recipient sheet to "Date format" as a requirement, the workflow fails.

I have endeavoured to resolve this with Smartsheet support, but due to the time differences, this has been a nightmare to address.

parulmishra

Yes I did also face the same issue.. as a workaround I changed the format of Date in the Source excel sheet to DD/MMM/YYYY where we can see the name of the month. Then Data Shuttle pulled correct dates into smartsheet

andrew.taylor37161

Hi Parul

I have been through the process of changing the source data, but any Data Shuttle workflows make changes when the dd is lower than 13. (12/01/23 becomes 01/12/23).

Basically I believe that because Data Shuttle doesn't have a "settings" section, it uses the US date format on dates that have a day number lower than the available month number.

I have however been working with Ani Shinde (Customer Success Manager - In Australia) and he created a helper date column (not restricted) beside the original date column and using the following formula has resolved the issue.

=IF(LEFT([Original Date]@row, 2) < 13, MID([Original Date]@row, 4, 3) + LEFT([Original Date]@row, 3) + RIGHT([Original Date]@row, 2), [Original Date]@row)

This formula will create the same issues in reverse if Data Shuttle corrects the original issue and doesn't inform everyone.