Dates and times look great in Excel until you try to concatenate them within a formula. Then you see Excel’s messy raw date and time values. I have found it necessary to use the YEAR, MONTH, DAY, HOUR, and TIME functions to extract these values and then format them for a SQL query.
For example:
First attempt (didn’t work)
="INSERT INTO `tour_dates` SET `datetime`='"&D2&" "&E2&"';"
The result:
INSERT INTO `tour_dates` SET `datetime`='38260 0.375';
Boo!
Second attempt (worked!)
="INSERT INTO tour_dates SET `datetime`='"&YEAR(D2)&"-"&MONTH(D2)&"-"&DAY($D2)&" "&HOUR(E2)&":"&MINUTE(E2)&":00"&"';"
The result:
INSERT INTO tour_dates SET `datetime`='2008-10-1 9:0:00';
Yay!