CODEWITHSUNDEEP
mysqlexcel
yarek
yarek

Reputation: 12034

excel format date for mysql

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

Upvotes: 14

Views: 32659

Answers (4)

jpprade
jpprade

Reputation: 3664

For the hours, minutes and second

go in the custom category and enter this :

yyyy-mm-dd hh:mm:ss

Upvotes: 17

mgalka
mgalka

Reputation: 171

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)

Upvotes: 0

Andrew
Andrew

Reputation: 9180

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.

Upvotes: 7

Scott Cranfill
Scott Cranfill

Reputation: 1056

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!

Upvotes: 17

Related Questions