Date Format in Data Shuttle Upload

Matthew_Lanterman

Hello,

I have a date column in a source sheet formatted as YYYY-MM-DD, and when it imports Smartsheet does not recognize the data as a date, even though the source sheet column is a date column. The impact of this is I cannot build reports or filters based on date ranges.

Is there something I can do in order to make Smartsheet know that is a date after import?

Paul Newcome

You're going to need to insert a helper column (date type) with a formula to pull the data over.

=DATE(VALUE(LEFT([Column Name]@row, 4)), VALUE(MID([Column name]@row, 6, 2)), VALUE(RIGHT([Column Name]@row, 2)))

Paul Newcome

You're going to need to insert a helper column (date type) with a formula to pull the data over.

=DATE(VALUE(LEFT([Column Name]@row, 4)), VALUE(MID([Column name]@row, 6, 2)), VALUE(RIGHT([Column Name]@row, 2)))

Matthew_Lanterman

Thank you Paul. Works perfectly.

If I had to explain how you came about this solution:

Knowing we needed a date, you started with DATE, and knowing the existing values in the cell was text, used VALUE to convert each section to a number. Then split the year using LEFT, Month using MID, and Day using RIGHT.

Is that the general idea?

Paul Newcome

That is correct. I am also making the ssumption that year will always be 4 digits and month/day will always be 2 digits (01, 02,.....10, 11, etc.).