Excel import, date format wrong?

[#1]

Dear all,

I am trying to import an Excel-file into Sense. Everything seem to work well, except for the date format. When I do the quick import, Sense converts my dates to the numerical value. See included screen dumps. What can I do to make me get the correct date format? I have tried to go via csv-format, and the dates are then correctly displayed, but that gives me the issue of not converting all the package IDs (column A) correctly i.e they are truncated, and are useless for my analysis.

Look at the column Created Date (column D) and you will see that I do not get the real date but the numerical representation. That is of course of no use for me. All date fields get the same conversion (error).

Please advise

brgd

Magnus

[rubenmarin]

Hi Magnus, dates are numbers, you can apply a format, if you use in script Date([CREATED DATE], 'YYYY-MM-DD') it will save the date visible as the Excel format.

Even being visible in that format, internally for QV will still be a number.

[#3]

Ok, where in my import script should i put that? I am not yet an avid script writer so bear with me

[rubenmarin · Accepted solution]

If you edit the script, in the left panel should be a tab with the excel name, inside this, the script will be something like:

LOAD PACKAGEID,

     SHIPEMENT...

...

     "CREATED DATE"

...

You need to change this "CREATED DATE" for:

Date("CREATED DATE", 'YYYY-MM-DD') as "CREATED DATE"

[#5]

Thank you very much, worked just fine. Also learned a little about how to write the scripts, very helpful