Service Desk Practitioners Forum
cancel

Date Problem - Importing SQL Data to Excel

Highlighted
Craig Lloyd
New Member.

Date Problem - Importing SQL Data to Excel

Hi all,

I have set up an Excel Spreadsheet that imports data from the service desk sql database using the 'external data' function.

The problem is that the dates being retrieved from the db are in the format 5/12/2005 12:29:59 AM

I have set excel to display the dates as short dates, ie 05/12/2005 without the date but whilst the date displays correctly if the cell is selected the original date format is still displayed.

This is preventing me from creating rules and using counting formulas etc

Can anyone help???? Please....
2 REPLIES
Guenther Schere
Honored Contributor.

Re: Date Problem - Importing SQL Data to Excel

Hi Craig,

as I understand, your problem is that excel doesn't provide the date in the correct format.

As I know you have to correct the format in excel before importing.

I use something like:
SQL --> export to excel --> edit file with excel macro --> import to SD.

Because I want to automate this process, I've wrote a excel macro to edit the orginal excel file to my wished output format.
After that I could import the date easily.

If you are not used to write macro, no fear, it's more easy than other programming languages.
You have just to re-arrange the values.

Best Regards
Guenther
Chris Bailey_3
Outstanding Contributor.

Re: Date Problem - Importing SQL Data to Excel

Hi Craig,

If you need to have the data formatted differently, you could create the following formula for the cells in another column:

=CONCATENATE(MONTH(A1),"/",DAY(A1),"/",YEAR(A1))

Assuming that the date was in cell A1, that should work. Post back if you're looking for something different.

Thanks,
Chris