Data Shuffle Date Formatting Issues

Jack Parry
Jack Parry ✭✭✭✭

Using Data Shuffle to import data from excel into Smartsheets on a daily basis.

When there are dates in the column, sometimes but not every time the date format will shift between US (MM/DD/YYYY) and UK/EU Date format (DD/MM/YYYY) when uploading.

Has anyone come across this issue before and how I go about resolving?

Smartsheet:

Excel:

This proves frustrating when using Smartsheets for calculating overdue actions!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer βœ“

    Hi @Jack Parry

    The first thing I'd check here is my personal account settings to ensure I've got the preferred date format selected for my account.

    Then the next thing I'd recommend doing is adjust how your dates are displayed in the CSV format. Data Shuttle uses the Smartsheet API, which prefers a date format of yyyy-mm-dd. If you can adjust this date field to display in that order in your CSV then Data Shuttle should be able to better recognize which value is the month and which one is the day. (And perhaps check the region setting of the CSV date field as well!!)

    Let me know if that helped. πŸ™‚

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer βœ“

    Hi @Jack Parry

    The first thing I'd check here is my personal account settings to ensure I've got the preferred date format selected for my account.

    Then the next thing I'd recommend doing is adjust how your dates are displayed in the CSV format. Data Shuttle uses the Smartsheet API, which prefers a date format of yyyy-mm-dd. If you can adjust this date field to display in that order in your CSV then Data Shuttle should be able to better recognize which value is the month and which one is the day. (And perhaps check the region setting of the CSV date field as well!!)

    Let me know if that helped. πŸ™‚

    Cheers,

    Genevieve

  • Matthew LeMasonry
    Matthew LeMasonry ✭✭✭

    Hi Jack,

    I had the issue where my data seemingly randomly, changed between UK and US format within the sheet. I eventually changed my data to break down into days months and years to stop data changing which seems to have worked.

    I now though have a very similar issue where my data has now changed so shows the date in American format after importing through data shuttle. All the regional settings are set to UK. I have even tried bringing into a new data table to try and resolve this new Smartsheet API which prefers a date format of yyyy-mm-dd.

    There seems to have been a change in Smartsheet the last few weeks, as this was working fine for months. I have a support case pending.


    Kind regards,


    Matthew

  • Jack Parry
    Jack Parry ✭✭✭✭

    Hi all,

    This issue seems to be a result of the outage from a few days ago. All seems to be fixed now :)

  • Jonathan_p_o_ITM
    Jonathan_p_o_ITM ✭✭

    I am still getting this problem with a workflow that used to work perfectly. Nightmare!