Date format??

[qlikuser222]

Hi everyone,

I have a file where the date format is in both MM/DD and DD/MM.

I want to pull the file in Qlik with DD/MM format but it is not as expected.

PFA and please help me.

Thanks

[effinty2112]

Hi Nicholas,

Try:

LOAD

Date(Data,'DD/MM') as Data,

     Status

FROM

test.xlsx

Good luck

Andrew

[qlikuser222]

HIm

Thanks for reply.

Qlik is reading 01/06 (Jan ,6th) as May 1st.

This is because inconsistency in the Data column in excel file. How can we fix that?

Thanks

[sunny_talwar]

I am seeing 1/6/2017 as Jan 6th only...

Screenshot is from your attached application.... I just changed the date format on the numbers tab

[qlikuser222]

Thanks for reply,

How about the

13/03/2017 Qlik is not considering its as Date.

I want to show it as Mar/13/2017

Thanks

[effinty2112]

Hi Nicholas,

                   When you look at your data can you tell if a value is formatted one way or the other? If something about another field can be used as a condition to tell QV how to format it then you could have something like

if(condition is met,  Date(Data,'DD/MM') ,Date(Data,'MM/DD')) as Data

Cheers

Andrew

[sunny_talwar]

Try using this before you load your table

SET DateFormat='DD/MM/YYYY';

[sunny_talwar]

SET DateFormat='DD/MM/YYYY';

LOAD Data,

    Date(Data) as Date,

    Status

FROM

[test.xlsx]

(ooxml, embedded labels, table is Sheet1);

[stigchel]

I seriously doubt the integrity of the data, even if you format certain dates differently, you still end up with dates in e.g. december 2017 with a status complete. If this is incorrect then I see no way of correcting that with the current data.