Data Shuttle Exports Date Field to CSV with Incorrect Formatting

Shane Brown

I have a Smartsheet with a few date fields that show as MM/DD/YY (I am in the US and this is standard for us). When I double click the cell is shows as MM/DD/YYYY which is how I need it appear in my CSV after Data Shuttle exports it. I do so and the CSV file shows MM/DD/YYYY when i open it in Excel. However, when I upload the CSV to service I'm using it for, the dates get messed up. I contacted their tech support who told me it's showing on their end as YYYY-MM-DD which is how I see it when I open the CSV as a TXT file in Notepad. Is there a way to make it so that the date lands in the CSV as MM/DD/YYYY?

Kerry St. Thomas

The YYYY-MM-DD tends to be an "under the hood" standard for handling dates - it allows for the same file to be opened on a computer with MM/DD/YYYY format selected, and another with DD/MM/YYYY. I just did a test, and was able to see (the same as you) the base format is YYYY-MM-DD.

If you can't configure the upload to the other service to read the current CSV output, it's workaround time. Is it possible for you to do an upload to your other service that parses out separate fields? I'm thinking you might need to add separate columns YEAR, MONTH, and DAY inside Smartsheet and then use that output to pull those three columns either in concert or separately into your other service. There's got to be other methods, but this is the one that pops to mind.

Alternately… have you investigated Bridge? It's a pretty snazzy premium app that allows for direct API between Smartsheet and several other services. I admit I haven't used it a lot, but I've seen it in action.

Shane Brown

Thanks for you response. As I was fiddling with it, I found that I could convert the date to a text string and keep the format I needed. In Excel this can be done with TEXT() but seeing as there is no TEXT() in Smartsheet, I was able to use LEFT() and just pull all 10 characters. So =LEFT([Date Field], 10) for a MM/DD/YYYY date, and that seems to be working!