Dates imported from Excel in YYYY-MM-DD are interpreted incorrectly

jeremy7392

I am attempting to paste or import data from Excel. I formatted dates in the ISO standard YYYY-MM-DD to prevent any confusion over which part is the month and which is the day. Yet, when I paste or import this data to Smartsheet, it interprets the day and month wrong whenever both are below 12.

Copying from Excel:

When pasted into a number/text column in Smartsheet: (correct)

When pasted into a date column in Smartsheet: (incorrect)

Is there any way to fix this issue? How do I need to format dates to prevent confusion on import?

Brian Wilson DC

@jeremy7392 Jeremy, date formatting is a little interesting in Smartsheet. After you set your date column on the upper right hand of the tool bar is a formatting area that will allow you to select the type of date display you want for that column. It is the small calendar icon. Before putting any data into that column select the date format you'd like to apply to the column. Then import your data or paste your data. If you select your ISO standard option, which is about halfway down, when you copy or import the data it is not going to display in ISO standard, but it will retain the correct Day, Month, Year. For instance:

2025-04-11 when pasted will appear as 04-11-2025.

After you paste all of your date or import it, simply select that column and reapply the date filter and it should convert everything into ISO standard if that's how you want to view it going forward. If you don't need ISO standard, and viewing it as 04-11-2025 is fine, then you're all set.

EDIT: It seems that if you are taking data from a previously DATE formatted cell it will actually hold the formatting and not convert it out of ISO standard, as long as you've pre-formatted the destination column. For instance, I made a second date column on my Smartsheet and entered a date in ISO Standard. Copied that date to another date column setup as ISO standard and the data format held true. I also just confirmed that taking data from Microsoft Excel that was formatted in ISO standard will copy and paste/import correctly into Smartsheet as long as you previously formatted that column.

I hope this helps!

jeremy7392

The date columns I am using are in the DD-MM-YYYY format so that other people using my reports have an easier time reading the dates. (They are unfortunately not used to ISO.) It seems like it should not be difficult for Smartsheet to properly interpret and convert an ISO date to another format.

jeremy7392

For what it's worth, this seems to a be a problem when converting from a text/number column to a date column as well. Pre-conversion:

After changing the column type to Date:

I don't understand why Smartsheet can't look at the whole column and understand what date format the column is in. Since there is no text-to-columns function in Smartsheet I can use to fix a whole column of dates at once, this is very frustrating.