excel format date for mysql

Question

My excel sheet has the following format : dd/mm/YYYY

ex: 24/10/2010 (european format)

and my mysql needs: YYYY-mm-dd (US format)

How can I format the date in Excel to have this US format ? I tried with cell format, but I don't have the desired format

Regards

Answer 1

Here's how you can find this Number format in Excel:

1. Go to Format Cells and then the Number tab.
2. Select Date
3. Change the Locale to English (U.K.)
4. The fifth item on the list (on my U.S. version of Excel) is the format you're looking for.

Hope that helps!

Answer 2

For the hours, minutes and second

go in the custom category and enter this :

yyyy-mm-dd hh:mm:ss

Answer 3

Use the text function -- assuming that the date to be converted was in cell A1, =TEXT(A1,"YYYYMMDD") will get your data into a format that mysql will understand as a date.

Answer 4

There are many solutions, but I reformat the dates for mysql using this formula (assuming your date is in cell E2):

=YEAR(E2)&"-"&RIGHT(TRIM(100+MONTH(E2)),2)&"-"&RIGHT(TRIM(100+DAY(E2)),2)