dates format issue

[Krish2459_58]

Hi ,

I have dates in the below format and when I apply the date conversion (DATE([Lease Commencement],'MM/DD/YYYY')) the left side dates are not visable. Please suggest.

[Krish2459_58]

attached the actual data for date.

[Lech_Miszkiewicz]

Hi @Krish2459_58 

make sure you study what Date() and Date#() functions do. 

• https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/Interpr...
• https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/Formatt...

There is significant difference between them

Now records at the top are not recognized as date/numerical types, and instead they are just loaded as text. You can convert them to dates using Date#() function before using Date() function to apply mask on the numerical value of the date.

cheers

[Krish2459_58]

@Lech_Miszkiewicz ,

I have tried this to convert : Alt( Timestamp#([Lease Commencement],'M/D/YYYY h:mm tt')) as [Lease Commencement]

I got this. Did I miss anything here.

[lennart_mo]

Hi @Krish2459_58 ,

You got the idea partly right. You need to add the second date format to your Alt() Function. As you can see it transforms your specified date format into numbers but not the other one. So applying something like this formula:

=Alt(Date#([Lease Commencement],'M/D/YYYY'),Date#([Lease Commencement],'DD/MM/YYYY') as[Lease Commencement]

Should give you all dates as number values. To transform it into dates just add the Date() function before the Alt() with your desired date format.

[TauseefKhan]

Check by loading this In your script:

DATE(DATE#([Lease Commencement],'DD/MM/YYYY')) AS [Lease Commencement],

[Lech_Miszkiewicz · Accepted solution]

Where are you from and what date region/format is used in your region. 

You are trying to Convert date which hase DAY/MONTH/YEAR using american - MONTH/DAY/YEAR and timestamp format?

Since you are loading data from excel this can cause a lot of issues an problems as excel itself will try to convert values to dates and depending in which region you open it it may be say 1st of March 2024 or 3rd of January 2024..

As you can see the same issue we have directly in Excel:

Anyway - I have loaded your data and I didnt have any issues.

Try loading this data using below script and inspect in data model viewer how fields are loaded:

[test]:
LOAD
	[Lease Commencement]
	,Text([Lease Commencement] ) AS [Text Lease Commencement]
    ,Date([Lease Commencement] ) AS [Date Lease Commencement]
    
	,Date(Alt(
    	[Lease Commencement]
    	,Date#(Trim(Text([Lease Commencement])),'D/M/YYYY')
        ,Date#(Trim(Text([Lease Commencement])),'D/MM/YYYY')
    	,Date#(Trim(Text([Lease Commencement])),'DD/MM/YYYY')       
        )) 
        						AS [Fixed Lease Commencement]
 FROM 
 	[lib://DataFiles/Test (1).xlsx]
(ooxml, embedded labels, table is test);

Also check what are the MAIN variables settings:

cheers