Qlik Sense

[aheavy95]

Hi all

In a project im working on, I pull data about clients from an excel sheet:

The problem I have is with the date column. For some reason for about 4 rows out of around 100, 

The date column in Qlik Sense appears as date number (45170) instead of the 09/01/2023 vale as in the excel above,

whereas for the rest it appears normaly ( like the 29/01/2023) 

If I try put date() around the "date number" it switches the month and day position (01/09 instead of 09/01)

what can I do to make those few dates appear correctly? 

Thanks 

[Or]

Date() allows you to specify the output format. If you do not specify one, e.g. Date(YourDate, 'DD/MM/YYYY'), it will use the default date format for the app, usually specified like this (and can be modified to suit your desired format):

SET DateFormat='M/D/YYYY';

And if no DateFormat has been specified, I believe it will default to the regional settings of the Qlik Sense  server.

[aheavy95]

Hi Or

a DateFormat had been set exactly as you mentioned: 

SET DateFormat='M/D/YYYY';

But still, I get the dd,mm  switched in positions for the few values that  are extracted as date numbers from excel 

Any idea?

[Or]

If you use DateFormat='M/D/YYYY', you will get the standard American format with the month first. If you don't want that, change the DateFormat to whatever you want, e.g. DD/MM/YYYY.