Datatable Date errors - wrong format in smartsheet but data is correct in table and CSV

Matthewrh

So I have been through this repeatedly, I have checked the raw data from the CSV file and the date format is dd/mm/yyyy the data format in the datatable for the date field is set to that format and the data shows correctly in the datatable.

My region is set correctly and the date format is set right in my profile. I have created a smartsheet and datatable connection to show the data in the columns, the column properties have the date type set.

However the date format for the data is incorrect, it is transforming the data from the correct format back into the American format which means all my data is displaying incorrectly and the filters I have setup dont work because the data is incorrect.

Anyone else having this same issue?

Matthewrh

I figured it out in the end with a bunch of searches on the community forums, no thanks to smartsheet support which I have to say was painful, the way they keep changing support staff everytime you reply to a ticket means you have to start over and explain everything again each time.

Datatable expects the date to be formatted in the ISO 8601 date format and anything else despite a dropdown on the datatable date field to allow you to select what format you are inputting into the field that doesnt seem to apply or work properly, the only accepted format is IS0 8601.

It will let you put the data in and it looks like its working but you try and display that data in other parts of smartsheet and you get strange unpredictable behaviour.

Put your dates into datatable in this format yyyy-MM-ddTHH:mm:ssZ and everything just works otherwise its a hot mess.

JamesB

@Matthewrh Unfortunately Smartsheet while an international product, will set the date format based on your region in your profile. See below for the formats available in the US.

The only way I have found around this without changing your region, is to create a text helper column with a column formula.

=Day([Date Column]@row)+"/"+Month([Date Column]@row)+"/"+Year([Date Column]@row)

Matthewrh

I figured it out in the end with a bunch of searches on the community forums, no thanks to smartsheet support which I have to say was painful, the way they keep changing support staff everytime you reply to a ticket means you have to start over and explain everything again each time.

Datatable expects the date to be formatted in the ISO 8601 date format and anything else despite a dropdown on the datatable date field to allow you to select what format you are inputting into the field that doesnt seem to apply or work properly, the only accepted format is IS0 8601.

It will let you put the data in and it looks like its working but you try and display that data in other parts of smartsheet and you get strange unpredictable behaviour.

Put your dates into datatable in this format yyyy-MM-ddTHH:mm:ssZ and everything just works otherwise its a hot mess.