Date CSV File keeps changing

[iainhmunro]

Morning

I have searched high and low and cannot find a real solution to my problem which is:

I have a CSV file - this is a request by my client for uploading purposes, so we cannot change to Excel. We enter the date and change the format - everything looks good. We save and do the upload, but when my client opens it at the other end, the date format has changed back to the way it was when we received it.

Is there a way to prevent or make it easy to change ?

I have tried adding in fields to look at the date and change it, but even that changes when you open it again.

Any help or guidance would be appreciated.

Regards

Iain

 

[Andrew Poulsom]

Then you are out of luck with dates where day and month are ambiguous.

 

[thisoldman]

Have you tried the TEXT() function?

Excel 2012
	A	B	C
1	Serial Date	mm/dd/yyyy	dd/mm/yyyy
2	2014-08-15	08/15/2014	15/08/2014
3	2014-09-15	09/15/2014	15/09/2014
4	2014-10-15	10/15/2014	15/10/2014
Book1

Cell Formulas
Range	Formula
B2	=TEXT(A2, "mm/dd/yyyy")
C2	=TEXT(A2, "dd/mm/yyyy")

Contents of csv file when checked with the editor Vim:

Serial Date,mm/dd/yyyy,dd/mm/yyyy
2014-08-15,08/15/2014,15/08/2014
2014-09-15,09/15/2014,15/09/2014
2014-10-15,10/15/2014,15/10/2014