Datashuttle to Smartsheet date parsing

Matthewrh

So I have come across another new fun issue that is quite a conundrum, I know that datashuttle wants the date in this precise format to work with data table ("yyyy-MM-ddTHH:mm:ssZ") but recently I have had a similar problem that I encountered when working with data table.

The date gets swapped around when there is a possibility that it can be an American format date, e.g. 05/03/2024 is changed to 03/05/2024 or 11/04/2024 changes to 04/11/2024.

I successfully got around this in data shuttle > data table by using the date format mentioned above which works just fine there, however when you do data shuttle > smartsheet it inputs any data as "yyyy-MM-ddTHH:mm:ssZ" and doesnt recognise it as a date until you edit the column properties and switch from date to text and then back to date and it parses "yyyy-MM-ddTHH:mm:ssZ" as a proper date.

Any new data added after this does not parse and you have to repeat the process over again, why is it that smartsheet/data table/data shuttle have such a hard time working with dates.

And I really dont want a "helper" column, I am trying to work with fairly large data sets that hit the limit of my smartsheet fairly regularly so I need to keep the number of columns and rows as low as possible.

Isaac A.

Hello @Matthewrh!

I checked in with our support team and it looks like you've opened a ticket on this, thank you. I'd like to reiterate some helpful information our agent provided in case it helps others who come across this post. 

When it comes to inserting dates into Data Table via Data Shuttle, it's essential to adhere to the specific format known as ISO8601 ("YYYY-MM-DD HH:MM:SS."). This format ensures compatibility and facilitates functionalities such as filter creation within Data Table.

This format, known as ISO8601, is outlined in our help center articles here:

Create and populate a DataTable

DataTable FAQs : How do I create a date field in my DataTable?

Also, this discussion provides a lot of helpful insights about this topic.

While I couldn't find other users reporting the same issue, if your problem persists, please keep following up with our support team so they can troubleshoot your specific scenario through a private channel.

I hope this information helps!

Cheers,

Isaac.

Matthewrh

Hello @Isaac A.

This is a different issue not in relation to Data Shuttle > Data Table.

I have noted the same date issue is happening when I do Data Shuttle > Smartsheet itself, this wasnt happening previously but is now taking place so when I use a date format DD/MM/YYYY it will swap around the day and month when the date is 12 or less so it ends up as a garbled mess.

So having gone through this I changed the date format on my report to ISO8601 ("YYYY-MM-DD HH:MM:SS.").

However smartsheet does something else different than Data Table, it does not parse the ISO8601 ("YYYY-MM-DD HH:MM:SS.") into the date format set on the sheet, it just shows the ISO8601 ("YYYY-MM-DD HH:MM:SS.").

Until you edit the column properties, change it to text and then change it back to date and magically it parses the ISO format into the date format set on the sheet.

However new data when it is entered goes back to being ISO8601 ("YYYY-MM-DD HH:MM:SS.")

Brian_Richardson

Sorry Matthew the only way I know how to resolve this is to have a second column in Date format and use the DATE function to extract the desired date from the ISO 8601 format. PITA!