Ask Format Date.

[#1]

Dear All,

I create qvw doc that load data from excel file. but some data is different format in one field (format date).

How to equalize the format date from this excel file without edit formate date in the excel file.

hope someone help me.

Regards,

Ted

[PrashantSangle]

Hi,

Use Alt()

Syntax with description from Help Menu

alt(case1[ , case2 , case3 , ...] , else)

The alt function returns the first of the parameters
that has a valid number representation. If no such match is found, the last
parameter will be returned. Any number of parameters can be used.

Example:

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

Will test if the field date contains a date according to any of the three specified date formats.

If so, it will return the original string and a valid number representation of a date.

If no match is found, the text 'No valid date' will be returned (without any valid number representation).

Regards

[sunny_talwar · Accepted solution]

This seems to work:

SET DateFormat='D/M/YYYY';

Table:

LOAD [ENTRANCE DATE],

    [EXIT DATE],

    company

FROM

ask.xlsx

(ooxml, embedded labels, table is Sheet1);

[sujeetsingh]

You can use two ways to do this.

One is just format it to date and see the different values.

The next way is use conditional conversion based on the deliminter ., that means when it is '-' then one format else the other format.

[#5]

Dear all,

thank you for your help,

regards,

Ted.