Data Shuttle Duration Not Uploading correctly

Michael Culley

When I try to upload an excel workbook that has duration columns, it changes all of them to a default of "1899-12-31". How do i fix that?

Genevieve P.

Hi @Michael Culley

I've been able to replicate what you're seeing when bringing through Excel data that's formatted as Time. Since Smartsheet does not have a Time column or function, it is currently expected that the value is unable to be recognized by the Smartsheet Text/Number column and appears as a single value (currently "1899-12-31", as you've found).

When you have a moment, please submit your feedback around Data Shuttle to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community. This will allow other users to vote on your enhancement idea! You may also want to vote on this other thread discussing Time in general.

In the meantime, I've successfully transferred data like this from Excel to Smartsheet using Data Shuttle when I changed the Excel data to be stored as text (instead of time), or used a helper column in Excel to translate the time to text.

Cheers,

Genevieve

Michael Culley

For more information, the duration column on excel looks like 00:01:55.

But no matter what, each cell is replaced with 1899-12-31

The column type is text.

Genevieve P.

Hi @Michael Culley

I've been able to replicate what you're seeing when bringing through Excel data that's formatted as Time. Since Smartsheet does not have a Time column or function, it is currently expected that the value is unable to be recognized by the Smartsheet Text/Number column and appears as a single value (currently "1899-12-31", as you've found).

When you have a moment, please submit your feedback around Data Shuttle to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community. This will allow other users to vote on your enhancement idea! You may also want to vote on this other thread discussing Time in general.

In the meantime, I've successfully transferred data like this from Excel to Smartsheet using Data Shuttle when I changed the Excel data to be stored as text (instead of time), or used a helper column in Excel to translate the time to text.

Cheers,

Genevieve

Michael Culley

@Genevieve P.

How did you change the data to be text instead of time? I've tried so many things such as changing the column type, using =text(), etc and I can't get it to work.

Genevieve P.

Hi @Michael Culley

You would need to adjust the cell-type within Excel, like so:

Michael Culley

@Genevieve P.

I've done that and when I use data shuttle it still treats it as time.

Genevieve P.

Hi @Michael Culley

In this instance I would recommend copy/pasting directly from excel into the sheet, then. This should keep the format identical across both programs.

Michael Culley

@Genevieve P. Then that defeats the purpose of Data Shuttle because smartsheet has a limit of 500 rows to copy at a time. So i'd be copying and pasting over and over again.