Excel VBA Date in wrong format

Dan Nielsen

New Member
Joined
Jan 10, 2018
Messages
6
I need to export data from a excel file to a CSV file (to use for import in MailChimp) using VBA. I have dates in columns and in excel (* .xlsm) dates are correct, but after they are copied to * .CSV and I open the * CSV file again, some dates are not correct.


I have checked that all cells in the columns are in the format "date" in *.xlms but in *.CSV some is "standard" and some is "date".


Can I possibly Convert all dates into text in an easy way with a simple VBA command?
*.xlms*.CSV
29-02-2016
29-02-2016
25-09-2017
09-10-2017
06-01-2018
01-03-2016

<colgroup><col></colgroup><tbody>
</tbody>
2/29/2016
2/29/2016
9/25/2017
10-09-2017
01-06-2018
03-01-2016

<colgroup><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Excel's probably picking up your regional settings as set in Windows.
What date format do you use on your PC mm-dd-yyyy?

Does this work

=TEXT(A1,"dd-mm-yyyy")

and write that out to the CSV file instead of the date.
 
Last edited:

Dan Nielsen

New Member
Joined
Jan 10, 2018
Messages
6
My PC is in dd-mm-yyyy

alle dates in *.xlms are cells with "date" format. But when i open the *.CSV in excel some are in "date" format and some are in "standard" format.
I need all date/dates ind alle cells of the columns to be mm-dd-yyyy. I don't think is matters if it is "date", "text", or "standard" format. It just need to be the same
it needs to be done in VBA
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Open the CSV file using an editor so you see what the date actually looks likes otherwise Excel will assume things.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,578
Members
410,853
Latest member
Tramp71
Top