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?
[TABLE="width: 500"]
<tbody>[TR]
[TD]*.xlms[/TD]
[TD][/TD]
[TD]*.CSV[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 105"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]29-02-2016[/TD]
[/TR]
[TR]
[TD="align: right"]29-02-2016[/TD]
[/TR]
[TR]
[TD="align: right"]25-09-2017[/TD]
[/TR]
[TR]
[TD="align: right"]09-10-2017[/TD]
[/TR]
[TR]
[TD="align: right"]06-01-2018[/TD]
[/TR]
[TR]
[TD="align: right"]01-03-2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 90"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2/29/2016[/TD]
[/TR]
[TR]
[TD]2/29/2016[/TD]
[/TR]
[TR]
[TD]9/25/2017[/TD]
[/TR]
[TR]
[TD="align: right"]10-09-2017[/TD]
[/TR]
[TR]
[TD="align: right"]01-06-2018[/TD]
[/TR]
[TR]
[TD="align: right"]03-01-2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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:
Upvote 0
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
 
Upvote 0
Open the CSV file using an editor so you see what the date actually looks likes otherwise Excel will assume things.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top