Date adjusts 100 years back after 2049

JohnPomp

We are copying excel data into Smartsheet and the dates form 2049 forward revert back to 1949. We have adjust regional settings on the PC so Excel will not revert a date back to the 1900's until we get to 2075. However, when moving good Excel into Smartsheet it reverts the dates. There is no formula it is just a straight copy paste. Does Smartsheet give you the ability to not modify the century for future dates or is this a hard coded feature in the program?

ker9

Hi @JohnPomp

I'm guessing but it's possible Excel has the date as 1949 and not 2049. If you only see the "49" portion, you might try changing the format in Excel or look in the formula box to see the full date:

If I enter 1/1/49 in an excel cell, it will use 1949.

Hope this helps!

JohnPomp

Excel uses Windows regional date settings in Control Panel where you can adjust which year reverts to the 1900's when using 2 digits fore the year. By default it seems to be set at 1949 - 2049. I adjust that to 1975-2075 so 2 digits won't revert to the 1900's until i hit 1976 but when we move data to Smartsheets it seems to undo that adjustment.

https://learn.microsoft.com/en-us/office/troubleshoot/excel/two-digit-year-numbers

ker9

@JohnPomp - if you enter the full 4 digits for the year in Excel, do you have the same problem? I suspect Smartsheet is picking up what is behind the scenes and not what it looks like formatted in Excel. If I enter 1/1/2049 in Excel and then copy it to Smartsheet it does not change.

JohnPomp

4 digits are there but they only want to display 2. When they copy that data smartsheets only seems to be looking at the 2 digits and adjusting back.

ker9

@JohnPomp - what happens if you import your excel file into smartsheet - how does it handle the dates?

JohnPomp

I am waiting on the team to try a straight import -- i'll let you know.