Hello,
This is a stumper for me. I have two columns of type Date: ValidStartDate and ValidEndDate. Each one has the same respective date for each record: 1/1/2021 and 12/31/9999.
After I save the file as a standard CSV (not UTF-8 or other variants), when I open it in Notepad++ or any other text editor, it contains the dates as displayed in Excel: 1/1/2021 and 12/31/9999.
I gave this file to a user to update, and they accidentally saved the second column as 12/31/1999 instead of 12/31/9999. The first column was untouched (so stayed as 1/1/2021).
Now, when the file is saved as CSV and opened in a text editor, it contains the dates formatted with a truncated year: 1/1/21 and 12/31/99. This is a problem, as the file is being directly picked up from the save directory and processed by a data mapping application that expects a specific format (M/d/yyyy).
I can more or less understand why this would happen for the column that was changed, but not the column that was unchanged. Does Excel do some evaluation of whether it is possible to shorten the years to the last two digits across the spreadsheet (i.e. there would be no ambiguity across columns if the year format was shortened)? Or how is this possible?
Upon changing the second column back to 12/31/9999 (and again leaving the 1/1/2021 untouched), the file again saved with 1/1/2021 and 12/31/9999 respectively, the full year format.
Please let me know if you would like screenshots or file examples - I can post those if needed.
Thanks so much in advance for the assistance!
Alex
