Date Formats When Exporting to CSV

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
64,700
Office Version
  1. 365
Platform
  1. Windows
I have a query which I am exporting to a CSV file. I have some date fields. In the Query Designer, under Properties of these fields, I set their format to a custom format of mm/dd/yyyy so that when viewing the query, today's date would show up like: 09/28/2006

However, if I export the file to a CSV file, in the file the date appears like:
9/22/2006 0:00:00

Is there a way to get it to export in the same format I have it displayed in (i.e. 09/28/2006)?

I know I can use the FORMAT function and use "mm/dd/yyyy" as my format, but the potential problem with that is that converts it to a Text format, which means if have a Text Qualifier set, it surrounds this value with the Text Qualifier, even though it is supposed to be a Date field and not a Text field (so my value exports as "09/28/2006" instead of the desired 09/28/2006).

Thanks.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,450
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
Hi

If you are using the menu option File > Export > csv > Export then when the next dialogue box appears click 'Advanced' and you should see an 'Export Specification' dialogue box. There is an option to include leading zeroes in dates but this won't make the time go away. As you are aware you can remove the time using the Format function, but you can also remove the text qualifier within that 'Export Specification' dialogue box.

HTH, Andrew
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
64,700
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply Andrew.

I was just curious if there was an easy to get rid of the time piece without making it text, as removing the Text Qualifiers will also remove them from the "real" text fields.

Through testing, it looks like the program we are sending to will accept text qualifiers around the date, so I'll probably just end up using the FORMAT function.
 

Forum statistics

Threads
1,171,734
Messages
5,877,185
Members
433,237
Latest member
ala995

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
Top