Date Format : Interpretation issue

[jujucts]

Hi evrybody,

I am facing a strange issue, I should nromally not (according to myself)

I have dates in XL File (date format cells)

When I load it to QV i get numbers (OK) but I can't tranform it under date format.

I will appreciate your knowledge to solve this

here are the files

bedt regards

juju

[ashfaq_haseeb]

Hi,

Num# will convert string to number.

Regards

ASHFAQ

[jujucts]

Hi Kush

Thank you taht works.

Just explain why Trilm is needed because there is apparently no blank 

Juju

[jujucts]

Yes sorry, I clicked the wrong name

[jujucts]

Yes I know and normally date#() will interpret as well date and transform it

As my cells are in XL date format it should be directly interpreted by date

Juju

[Kushal_Chawda]

juju1204‌

What I understood using trim is that when you have numbers as text, you can simply use trim function to convert to actual number , no need to use num#

[avinashelite]

your month data is in text format , that why we need to use num#() and not the date#

Date#

date#(expression [ , format-code ])

The date# function evaluates the expression as a date according to the string given as format-code. If the format code is omitted, the default date format set in the operating system is used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Date format	YY-MM-DD	M/D/YY

date#( A ) where A=8/6/97 returns:

	Setting 1	Setting 2
String	8/6/97	8/6/97
Number	-	35648

date#( A, 'YYYY.MM.DD' ) where A=1997.08.06 returns:

num#(expression [ , format-code[ , decimal-sep [ , thousands-sep] ] ])

The num# function evaluates the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the default number format set by script variables or in the operating system is used.

Examples:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Number format	# ##0,#	#,##0.#

num#( A, '#' ) where A=35,648.375 returns:

	Setting 1	Setting 2
String	35,648.375	35648.375
Number	-	35648.375

num#( A, '#.#', '.' , ',') where A=35,648.375 returns:

	Setting 1	Setting 2
String	35,648.375	35,648.375
Number	35648.375	35648.375

num#( A, '#.#',',','.' ) where A=35648.375 returns:

	Setting 1	Setting 2
String	35648.375	35648.375
Number	35648375	35648375

num#( A, 'abc#,#' ) where A=abc123,4 returns:

	Setting 1	Setting 2
String	abc123,4	abc123,4
Number	123.4	1234

[jujucts]

Yes Avinash,

I read that too but i just don't understand why date# does not work in my case

01/04/12 is a date format

The date# function evaluates the expression as a date according to the string given as format-code.

so date#(Month, 'DD/MM/YY') should return a date

and date(date#(Month, 'DD/MM/YY'), 'DD.MM.YYYY')  should return 01.04.2012

so is my question : why this does not work ?

Juju

[swuehl · Accepted solution]

At what point of time in your script execution are you trying to parse the date?

Could you post your script?

As I said above, the CROSSTABLE will transform all column labels to text, and the column label is a textual number like 41030 when read from excel. so trying to interpret it with a date format will not work.

[jujucts]

Swuehl

That's an odd behaviour of the CROSSTABLE LOAD prefix, columns labels will always be transformed to text values.

that's the point I missed.

Thank you

Juju