Strange Number Formatting Issue

[jackiedaytona]

I have a strange formatting issue that I'm hoping someone will have an idea how to fix:

I have a csv template for EEO reporting that I need to fill out and then upload. The way you're supposed to enter the date range is in one long string as MMDDYYYYMMDDYYYY. For example, for the range October 10, 2020 thru October 23, 2020 you enter the number 1010202010232020. I created a file for 2020 and had no issue with the date range field. However, when I try to enter the correct range for 2019 (October 12, 2019 thru October 25, 2019) for some reason the last 9 gets converted to a zero so it displays as 1012201910252010. I have tried re-formatting the field as Text and General (which wouldn't matter in any case as it's a csv file) but that last 9 always flips to a zero. I tried entering the range in another field, any other field, on the file; the 9 always flips. I opened the file as a .txt file, converted the zero back to a 9, saved it as a .txt file and then went into excel and opened the .txt file... and the 9 flipped to a zero.

Does anyone have any idea what is causing this to happen and how I can fix it? I need to upload this file to the EEO site so I'm pretty much stuck with the template I have.

 

[jackiedaytona]

I have a strange formatting issue that I'm hoping someone will have an idea how to fix:

I have a csv template for EEO reporting that I need to fill out and then upload. The way you're supposed to enter the date range is in one long string as MMDDYYYYMMDDYYYY. For example, for the range October 10, 2020 thru October 23, 2020 you enter the number 1010202010232020. I created a file for 2020 and had no issue with the date range field. However, when I try to enter the correct range for 2019 (October 12, 2019 thru October 25, 2019) for some reason the last 9 gets converted to a zero so it displays as 1012201910252010. I have tried re-formatting the field as Text and General (which wouldn't matter in any case as it's a csv file) but that last 9 always flips to a zero. I tried entering the range in another field, any other field, on the file; the 9 always flips. I opened the file as a .txt file, converted the zero back to a 9, saved it as a .txt file and then went into excel and opened the .txt file... and the 9 flipped to a zero.

Does anyone have any idea what is causing this to happen and how I can fix it? I need to upload this file to the EEO site so I'm pretty much stuck with the template I have.

Edit: I meant to say I opened the .csv file in Notepad and then saved it as .txt, fixed the error, and then opened the .txt using excel..

 

[Toadstool]

Hi JackieDaytona,

Your challenge is that Excel sees all numeric for the date when it opens the file so treating it as General it only has 15 numeric precision so you lose the last digit. When you open the file use the wizard like this

...but on the next step select that date and make it Text