Microsoft Excel

Excel Lookup – Maintain Date Format

Consider the following spreadsheet in Excel:
hyundaialeague2010

I wish to perform a lookup on these fields to build a dirty INSERT into SQL list, however when I reference Cell A1 the date is converted into a number, like seen in the following Excel formula:

="insert into #matches2010 (eventdate, eventname) select '"&A1&"','"&B1&" vs " &D1&"'"

which outputs the following:

insert into #matches2010 (eventdate, eventname) 
select '40031','Melbourne Victory vs Central Coast Mariners'

I need the eventdate to reference the date as per the spreadsheet above. ie:

insert into #matches2010 (eventdate, eventname) 
select '2009-08-06','Melbourne Victory vs Central Coast Mariners'

To achieve this in Excel, use the TEXT function with the following format:

="insert into #matches2010 (eventdate, eventname) select '"&TEXT(A1,"YYYY-MM-DD")&"','"&B1&" vs " &D1&"'"

I hope this helps!

Mike250

Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Leave a comment

Your email address will not be published. Required fields are marked *