Strange Number Formatting Issue

jackiedaytona

New Member
Joined
Jun 17, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jackiedaytona

New Member
Joined
Jun 17, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,907
Office Version
  1. 2016
Platform
  1. Windows
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
1623967447952.png


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

1623967489547.png
 

Forum statistics

Threads
1,140,928
Messages
5,703,218
Members
421,283
Latest member
MacroBegin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top