-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,224
- Office Version
- 365
- Platform
- Windows
I think we will need more details.
What exactly does the field look like before you save it?
Is it really Date or Text?
What is the format of the cell?
How are you saving it?
How are you opening it (are you opening in Excel or a Text Editor)?
Hint: You should NEVER use Excel to open CSV file if you really want to see what is in there. This is because Excel does some automated conversions on the data. If you REALLY want to see what the data looks like in the CSV, open with a Text Editor like NotePad, WordPad, or some other text editor program.
If you need to open it Excel to work on it, open it this way:
Go into Excel first
Then go to the Data menu
Select Get External Data, and select the CSV/Text option (may vary slightly in different versions of Excel)
This should invoke the Import Text Wizard, where you can set the data type for each field.
If you do not want this field to change at all, use Text (instead of General or Date).
What exactly does the field look like before you save it?
Is it really Date or Text?
What is the format of the cell?
How are you saving it?
How are you opening it (are you opening in Excel or a Text Editor)?
Hint: You should NEVER use Excel to open CSV file if you really want to see what is in there. This is because Excel does some automated conversions on the data. If you REALLY want to see what the data looks like in the CSV, open with a Text Editor like NotePad, WordPad, or some other text editor program.
If you need to open it Excel to work on it, open it this way:
Go into Excel first
Then go to the Data menu
Select Get External Data, and select the CSV/Text option (may vary slightly in different versions of Excel)
This should invoke the Import Text Wizard, where you can set the data type for each field.
If you do not want this field to change at all, use Text (instead of General or Date).
Upvote
0
Thanks for the speedy response.
Before saving the data entry bar will show 12/1/2017. In the spreadsheet for that cell it displays 1-Dec.
After saving the data has been changed to 12/1/2018.
I have been opening using Excel 2010. I will try to open using Notepad. I have not found the Import "Text Wizard yet but will look further.
Thanks
Before saving the data entry bar will show 12/1/2017. In the spreadsheet for that cell it displays 1-Dec.
After saving the data has been changed to 12/1/2018.
I have been opening using Excel 2010. I will try to open using Notepad. I have not found the Import "Text Wizard yet but will look further.
Thanks
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,224
- Office Version
- 365
- Platform
- Windows
When you save as Excel file as a CSV, it saves the formatted version (you can see this if your view it in NotePad).
So, it will not save "12/1/2017", but rather it will save "1-Dec".
When you import a date (and that is really what you are doing when you open a CSV file in Excel, you are importing it) with no year component, it assumes the current year (2018).
So if you want to maintain the 2017 year, you need to change your date format to one that includes the year before saving it to a CSV file.
So, it will not save "12/1/2017", but rather it will save "1-Dec".
When you import a date (and that is really what you are doing when you open a CSV file in Excel, you are importing it) with no year component, it assumes the current year (2018).
So if you want to maintain the 2017 year, you need to change your date format to one that includes the year before saving it to a CSV file.
Last edited:
Upvote
0
