excel to mysql date and time conversion problem

[innergies]

I have searched and searched, but to no avail. Hopefully you guys will be able to help, so here goes...

I'm trying to export a 2000 excel sheet into mysql... that's no problem. What I am having a problem with is the date format. The database that I'm importing into (mysql database) is in the following date format: 20060416 (this would be April 16, 2006). The excel sheet that I have is in normal date format i.e. 04/16/2006. How can I convert the 04/16/2006 in my spreadsheet into 20060416 so that I can export it into the mysql database?

Second question, similiar to the first...
My time format in the database is 213000 (this would be 9:30 PM) and the format I have in my spreadsheet is 09:30:00 PM. Again, anybody know how I can convert 09:30:00 PM into 213000?

Thank you so much for your time!

 

[Aladin Akyurek]

=TEXT(A2,"YYYYMMDD")

=TEXT(C2,"HHMMSS")

 

[innergies]

How can I apply that to the entire column? I've got literally HUNDREDS of entries.

Thanks for the reply... it works perfect for a single cell though. At least I've got a direction now

 

[Aladin Akyurek]

How can I apply that to the entire column? I've got literally HUNDREDS of entries.

Thanks for the reply... it works perfect for a single cell though. At least I've got a direction now

If the entries of interest are in A from A2 on and the appropriate formula in B2, select B2, and apply a double click on the Fill Handle of cell B2. Then select again B2, apply Control+Shift+Down Arrow, run Edit|Copy. Then, select A2, appy Control+Shift+Down Arrow, and run Edit|Paste Special with the Values option checked.

 

[innergies]

THANK YOU so much! It worked perfect!