Quickly change m/d/yyyy dates to mm/dd/yyyy dates in Excel

Sometimes you get data in an Excel or csv file and it will be in a m/d/yyyy format.


Here is an example

1/1/2005
2/1/2012
2/12/2012
12/5/2015

This will look fine if you open the csv file in Excel but if you need to import this data into a database you might get an error that the format is not correct.

 What you want is the data to be in this format
01/01/2005
02/01/2012
02/12/2012
12/05/2015


If you use Format Cells and then pick Date you will see that there is no mm/dd/yyyy format What you have to do is pick the Custom Category and click on the m/d/yyyy format

Now change m/d/yyyy to mm/dd/yyyy

Click OK and you are all set, you will see that the dates are now in mm/dd/yyyy format

Let me just mention that the problem was not the conversion in SQL itself but a tool that did the import choked

BTW SQL Server can do this fairly easily

If I run the following

1 2 3 4 5 6 7 8 9 10 11

;with dates (date) as (select '1/1/2001' union all select '11/1/2001' union all select '1/12/2001' union all select '5/5/2001' union all select '1/22/2001' union all select '12/1/2001' union all select '01/01/2001' ) select convert(date,date) from dates

I will get this as output

2001-01-01
2001-11-01
2001-01-12
2001-05-05
2001-01-22
2001-12-01
2001-01-01

That of course is just a date, if you want mm/dd/yyyy format, you can use convert and style 101...

1 2 3 4 5 6 7 8 9 10 11 12 13 14

;with dates (date) as (select '1/1/2001' union all select '11/1/2001' union all select '1/12/2001' union all select '5/5/2001' union all select '1/22/2001' union all select '12/1/2001' union all select '01/01/2001' ) select convert(varchar(10),convert(date,date),101) from dates select convert(date,date) from dates

And here is that output

01/01/2001
11/01/2001
01/12/2001
05/05/2001
01/22/2001
12/01/2001
01/01/2001