Text to Date (format change)

[GeorgePhilips23]

Hi, 

I have exported excel file where the date column is in text format 

for example: 

September 07, 2021

July 24, 2021

August 22, 2021

December 31, 2021

February 24, 2021

Now in Qlik Sense I have to transform it into a date format of 'DD-MM-YYYY'.

Can anyone give a best formula to achieve that?

[Digvijay_Singh · Accepted solution]

Something like this may be - 

Date(Date#(DateField,'MMMM DD, YYYY'),'DD-MM-YYYY')

[GeorgePhilips23]

4 M's didnt work

[Digvijay_Singh]

Worked for me - 

[Digvijay_Singh]

[Digvijay_Singh]

Check if you missed any space somewhere, not sure though

[Zapparoli]

The solution provided by @Digvijay_Singh works, I did some testing and in the script section in the SET variable you need to change the format of LongMonthNames by the name of your excel format:

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

This way it will work.

[GeorgePhilips23]

Still didnt work!

[Zapparoli · Accepted solution]

George,

It seem that you are missing a space in your code, on the "YYYY" part.

try this:

Date(Date#(date_added,'MMMM DD, YYYY'),'DD-MM-YYYY') as OTT_Date

[GeorgePhilips23]

Yes, worked. Thanks a lot.