Excel Professional Plus 2013 converts random pasted values from dd-mm-yyyy to m/d/yyyy format

There's two sides to this question, SQL Server and Excel. I'm asking this question in an Excel forum because I'm pretty sure this stems from the Excel side.

I have a SQL Server select statement that returns -- among other things -- a column in [dd-mm-yyyy] format. Everything looks good in SQL Server Management Studio and when I export it as a CSV and view it in notepad. But whether I copy and paste into Excel (Professional Plus 2013) or import the file into Excel, the same symptom happens: random dates are converted from [dd-mm-yyyy] format into [m/d/yyyy] format.

My requirement is to give this report in Excel to the customer in the specified format with the date strongly typed as a date field (and not text).

Here two images (SQL Server and Excel) showing how the data changes. Is there some setting in Excel I can set to prevent this from happening?

Answer

This is not random. The dates that are keeping the format are being treated by Excel as Text (left aligned). The ones with different format are treated as numbers (dates - right aligned).

My guess is that your regional settings are set to use M/D/YYYY format. Hence, only dates that comply with this format are imported as number.

Examples 24-09-1998 does not comply with M/D/YYYY, therefore is treated as text. 01-06-2000 will be imported as date but it will be the 6th of January instead of the 1st of June.

Check your regional settings.

After correctly importing all dates as number (date) you can apply whatever date format you want. The one you want is dd-mm-yyyy.