Date Formats When Exporting to CSV

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,748
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
57,748
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,656
Messages
5,660,151
Members
418,554
Latest member
Avalanchez

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