text field keeps re-formatting to date in CSV

[john777]

Greetings everyone
I have a small but frustrating problem
I am trying to prepare a file for exporting into CSV format in order to transfer into a different application
For all date fields the format must be US and in following format mm/dd/yyyy. The problem is any date with a day number less than twelve only appears as mm/dd/yy. When you click on it, the full year appears but the other application will not accept it.
So for example a date which is 03/31/2015 will appear in full whereas a date which is 03/11/2015 appears as 03/11/15
I have tried re-formatting the fields as text which shows the full year but when I save as a CSV it will not save the new format. I have tried re-formatting the field to text, putting an apostrophe in front of the text before entering and using a formula TEXT(A1,"mm/dd/yyyy"). They all work but on closing the file and re-opening the field reverts back to a date field and the same problem re-occurs
does anyone know why this happens and whether it is curable
Many thanks for reading

 

[Joe4]

Welcome to the Board!

They all work but on closing the file and re-opening the field reverts back to a date field and the same problem re-occurs
does anyone know why this happens and whether it is curable

If it is a date field, and you use a Custom Date Format of "mm/dd/yyyy", then export/save your file as a CSV, it will work.

Where many people get tripped up is that they try to view the CSV using Excel. If you want to view what the actual data looks like in a CSV, NEVER use Excel! Excel converts the data again when it opens up the CSV (as long as you don't re-save that CSV file in Excel, those converted changed will NOT be permanent).

If you TRULY want to see what the data in a CSV file looks like, use a Text Editor to view it (like NotePad or WordPad or your favorite Text Editor). If you do that, I think you will see that you data appears as you want it to.

 

[john777]

Hello
I have kinda got round it by downloading libreoffice and using the spreadsheet on that. Basically the format doesn't re-convert everytime the CSV is opened.
The other problem on Excel is for any date where the day number is less that twelve ie 03/04/2014. The date is shown as 03/04/14 even though the custom date is set as mm/dd/yyyy. The application I am trying to transfer the data into then reads the year as two digits and rejects it. It all seems a bit perverse. Anyway can work round it now so thanks very much for reply