Export from MS Access to Excel changes date format from mm/dd/yyyy to dd-mmm-yy

[HWGuthrie]

My regional settings in Windows for short date format is mm/dd/yyyy. When querying in MS Access, the dates indeed shows up in that format. However, when I export to Excel (use options "Export data with formatting and layout" and "Open the destination file..."), the spreadsheet shows the dates in format dd-mmm-yy.

Clicking on the column header in Excel, the format shows "General". However, when I click on the individual cell, it shows "Custom" with "dd-mmm-yy". Of course, I can click on the column header and change it to Short Date format, but I have to do that for every column that has dates in it.

Does anyone know what is causing this and how to make it so my dates exported from Access come over in short date format?

P.S. I don't want to use FORMAT in Access because then I have to do that every time I query a date field. Plus, the date exports as a string to Excel instead of a date.

 

[Micron]

Your Access table date fields are Date/Time data type?

I don't want to use FORMAT in Access because then I have to do that every time I query a date field.

You are referring to where, exactly? Using a date format in the table fields should fix the problem if they are in fact date/time data type already. I'm suggesting Short Date but I can't think of why that would mess up your queries.

 

[HWGuthrie]

When talking about not wanting to use FORMAT, I'm referring to using the FORMAT function on the data in the query. For example, FORMAT([InstalledDate],"mm/dd/yyyy"). The problem with FORMAT is that it changes the data to string when exported, and I want to keep it as a date data.

In this particular case, I'm querying an external database. But, it doesn't matter because I can create a "Make Table" query using my query, and see that the data in that newly created table indeed has a Date/Time data type. When I view the data in the table, the dates have the format mm/dd/yyyy. However, when I export that table to Excel and view it, the data has the format dd-mmm-yy. Same thing as before. The column has a General format, and each individual cell has a Custom format.

Digging into that newly created table's design a little further, the design shows a blank format. As you suggested, I forced that to "Short Date" and saved it. Now, when I export it, the Excel file shows the mm/dd/yyyy format. So, we seem to be getting a little closer.

I guess the question now is: When querying an external database, how do I get a date field that shows in Short Date format (mm/dd/yyyy) in the query to export to Excel, retain that format, and not switch to dd-mmm-yy format?

 

[Micron]

When querying an external database, how do I get a date field that shows in Short Date format (mm/dd/yyyy) in the query to export to Excel, retain that format, and not switch to dd-mmm-yy format?

That implies you would create this table each time but you would not. Create once, apply the date format in the table field(s) and delete/append/update to this table thereafter.
EDIT - I suppose you could coerce the query field as in
Cdate(format(#01/01/2020#))

 

[HWGuthrie]

Thank you for the attempt. Yes, I could create a table with the correct formatting, dump the query into that table, and have it export correctly. But, those are extra steps that I was looking to avoid. Exporting straight from the query using CDate(Format()) still gives the same results. Going to chalk this up to a quirk between MS Access and Excel and quit trying. Thanks again.