Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem to load Date from Excel!

Hi,

The date on excel is a numeric value.

40452 is 2010-10-01 (YYYY-MM-DD)

While loading the 40452 from the Excel file, how can I load it as a date type value?

Best Regards,

Cassiano

12 Replies
Not applicable
Author

Use "Date(Excel_date_field) AS Date_Field" in your load script.

Not applicable
Author

Hi,

This function is not working.

Attached is the QV file.

Not applicable
Author

Here is the excel file that I'm using.

Not applicable
Author

Here is a link about the excel serial date

http://www.codeproject.com/KB/datetime/exceldmy.aspx

I hope that this can help.

Not applicable
Author

The combination of purgechar and date have worked.

date (purgechar(ExcelDate,'[]'),'DD/MM/YYYY')

Tks,

Cassiano

Not applicable
Author

Hi all

I have the same problem with a crosstab, I can't make a date. Have you a solution:

I have made this script

SourceXLS:
CrossTable(periode, montant, 3)
LOAD catégorie,
[sous catégorie],
détail,
[40179.000000],
[40210.000000],
[40238.000000],
[40269.000000],
[40299.000000],
[40330.000000],
[40360.000000],
[40391.000000],
[40422.000000],
[40452.000000],
[40483.000000],
[40513.000000],
[40544.000000]
FROM
C:\Users\paul\Documents\perso\Comptes.xlsx
(ooxml, embedded labels, table is Comptes, filters(
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null))
));

myDataFile:
NOCONCATENATE
LOAD *
,date#(periode,'MM/DD/YYYY') as myDate
RESIDENT SourceXLS

but it doesm't succedd

Please help me

Not applicable
Author

I had the same situation.

I have loaded the crosstable into a tempo table and made the purgechar...



Table1:

NOCONCATENATE

LOAD *,

date (purgechar(tempDate,'[]'),'DD/MM/YYYY') as Date

RESIDENT temp_Cross;



DROP

TABLE

temp_Cross;



DROP

FIELD

tempDate;







Not applicable
Author

Try this !

Change the formate of cell in excel sheet as Date *3/10/2001 and local as english(us)

date#



date#(tempData,'YY.MM.DD') as Data

you will get like this

is this ok

Naren.



(tempData) as TB,

then use this in script..

Not applicable
Author

date(num#(tempData,'########'),'DD/MM/YYYY') as date,

the above code will work fine.