Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cmccafferty
New Contributor

Date into workable format?

Hi All,

Can someone advise as to where I am going wrong with this:

=date([BD ReturnDate], ('DD/MM/YYYY'))

When download a table into excel with that in it, I get the date showing correctly, but I have to do Text-to-Columns if I want to be able to sort and filter it properly.

Thanks

Chris

Tags (1)
9 Replies

Re: Date into workable format?

You have an extra set of parenthesis around the date format... I would remove them and see if that works

=Date([BD ReturnDate], 'DD/MM/YYYY')

YoussefBelloum
Esteemed Contributor

Re: Date into workable format?

And if the above solution don't work, you can try this:

=Date(Date#([BD ReturnDate], 'DD/MM/YYYY'))


or this:


=Date(Date#([BD ReturnDate], 'DD/MM/YYYY'),'DD/MM/YYYY')

cmccafferty
New Contributor

Re: Date into workable format?

Tried all 3, none worked unfortunately.

Is my load script okay...?

qv2.png

YoussefBelloum
Esteemed Contributor

Re: Date into workable format?

the script seems to be ok, but the rest of the Date fields you're formatting, formatting works for these fields ?

cmccafferty
New Contributor

Re: Date into workable format?

Yep, they work fine.

I tried changing the script so that [BD ReturnDate] is in the same way. But still no joy

qv3.png

YoussefBelloum
Esteemed Contributor

Re: Date into workable format?

would you be able to show us via a sample app or an image what you have so far?  using that field in a listbox for example

Re: Date into workable format?

Note that in the expression:

Date(Date#([BD ReturnDate], 'DD/MM/YYYY'),'DD/MM/YYYY') AS [BD ReturnDate]

The first date mask (red) is the input mask (the one in which the value of the field is stored) and the second one (blue) the output mask (the one in which you want to display the value. They may not be equal if the input data is in American format, e.g.: M/D/YYYY, in which case, the expression would look like

Date(Date#([BD ReturnDate], 'M/D/YYYY'),'DD/MM/YYYY') AS [BD ReturnDate]

cmccafferty
New Contributor

Re: Date into workable format?

UPDATE...

Having changed the script, I just changed the table from =date([BD ReturnDate], ('DD/MM/YYYY'))

to simply [BD ReturnDate]

Thanks for all your help and suggestions.

sasiparupudi1
Honored Contributor III

Re: Date into workable format?

Is it working fine? if so, pl close this thread by marking any helpful and a correct answer.

if not

may be try breaking the date into

Month([BD ReturnDate])

Year([BD ReturnDate])

see if that woks

Community Browser