Excel/CSV date format

[Maghiran]

Hello,

I have an excel (.xlsx) file with a column with dates in this format - dd/mm/yyyy. I have to upload this file into an application which accepts only .xls or .csv files (and the date MUST be in this format). As most of the times the files are huge, I'm using the .csv format.

The problem is that when I convert my .xlsx file to .csv (file - save as - comma separated values file....), the dates are misinterpreted and the days become months.

How can I prevent this from happening? Doesn't the CSV format know this date format? Or does it take its format from the Regional settings from my PC?

Thank you

 

[John_w]

Record a macro with you doing the Save As... CSV, then edit the macro to add the Local:=True argument. It should look something like this:

Sub Macro1()
    ActiveWorkbook.SaveAs Filename:="C:\Path\to\folder\Book1.csv", FileFormat:=xlCSV, CreateBackup:=False, local:=True
End Sub

I don't think you can specify the local setting in the Excel GUI when saving, so the only way is to Save As via a macro as shown above to override Excel's default mm/dd/yyyy date format.

 

[K-MAN69]

I have the same problem and I incorporated the local:=True solution that is suggested above and I still am haveing the csv file created with dates in the wrong format (ie mm/dd/yyyy when I want dd/mm/yyyy)

any help would be great ..below is my code...

    Workbooks.Add    Sheets("Sheet1").Name = "SPI"
    ActiveWorkbook.SaveAs Filename:="C:\Users\Allalemdji\Dropbox\CSVdata\SPI.csv", _
        FileFormat:=xlCSV, Local:=True, CreateBackup:=False
    Windows("Matt Daily Data.xlsm").Activate
    Columns("A:F").Select
    Selection.Copy
    Windows("SPI").Activate
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWindow.Close SaveChanges:=False

 

[brucef2112]

I think this can be fixed by changing (forcing) the cell's format.
Select the column with the date. go to Format > Cells (it may already be set to DATE (which may be your regional setting. I'll assume it picks up your regional setting).
Scroll down and choose 'Custom' then set the Type: dd/mm/yyyy.

Depending on your regional settings, it may not look like nothing happened but now when you do a Save As CSV it will maintain the format you set under Custom.

 

[K-MAN69]

thanks a million that worked a treat for me