How can we convert two date format (eg.'DMY' and 'MDY') in one single format ('DD/MM/YYYY').

[Sandip]

Hi Team,

I have create sample data in excel sheet and i have put date data filed like in my excel sheet  'DD/MM/YYYY' format.

but after loading  data on Qlikview, Date filed it will consider Two format Like 'DD/MM/YYYY' and 'MM/DD/YYYY'

But i want Only one Single format Like 'DD/MM/YYYY'

below are the data;

Name	dept	date	Payment
Sandip	IT	15/05/2022	38715
Prasad	Banking	8/4/2022	31212
Chetan	IT	6/6/2022	42630
Rahul	Pharma	25/05/2022	26211
Shubham	Pharma	30/04/2022	38722
Kalu	Farmer	10/3/2022	49255
Mahesh	IT	11/3/2022	27317
Kaya	Analystics	13/02/2022	59694
Saurabh	Analystics	25/04/2022	46146
Pavan	Chemical	13/4/2022	32599

I have Try diff way so many time but still problem not sort out.

eg.

date(alt(date(date,'DD/MM/YYYY'),date(date,'MM/DD/YYYY')),Date(date,'DD/MM/YYYY'))

date(num(Date(date)))

num(date#(date,'DD/MM/YYYY'))

DATE(date#(date,'DD/MM/YYYY'),'DD/MM/YYYY')

Date(Alt(Date#(date, 'DD/MM/YYYY'), Date#(date, 'MM/DD/YYYY')),'DD/MM/YYYY') as Date

Please help me to resolve the my problem 

Thanks 

Sandip Chaudhari 

[salonicdk28]

Hi,

please try the below expression in your script once-

Date(Floor(Alt(Num(date), Num(Date#(date,'MM/D/YYYY')), Num(Date#(date,'DD/MM/YYYY')))), 'DD/MM/YYYY') as NewDate

Thanks,

Saloni

[Sandip]

Hi @salonicdk28 ,

It's Working but we are getting data like this ,

Name	dept	date	NewDate	Payment
Kaya	Analystics	13/02/2022	13/02/2022	59694
Pavan	Chemical	13/4/2022	13/04/2022	32599
Saurabh	Analystics	25/04/2022	25/04/2022	46146
Shubham	Pharma	30/04/2022	30/04/2022	38722
Sandip	IT	15/05/2022	15/05/2022	38715
Rahul	Pharma	25/05/2022	25/05/2022	26211
Chetan	IT	6/6/2022	06/06/2022	42630
Kalu	Farmer	10/3/2022	03/10/2022	49255
Mahesh	IT	11/3/2022	03/11/2022	27317
Prasad	Banking	8/4/2022	04/08/2022	31212

Here in the data we have date and NewDate filed. But sir i have put date format DD/MM/YYYY and  old date 10/03/2022 change like 03/11/2022. In this case old day is 11 as per i put date and after change date will be showing 03. so this we don't want .

we want as per old format as same in new also.

eg. old date 10/03/2022    and    NewDate   10/03/2022

Thank You for the support 

Thanks 

Sandip Chaudhari

[salonicdk28]

So, 

Kalu	Farmer	10/3/2022
Mahesh	IT	11/3/2022

are these in DD/MM format or MM/DD format, I thought these are in MM/DD hence I have changed those into DD/MM

[salonicdk28]

If you covert the date in the table directly, that is also working using below expression-

=Date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY')

[Sandip]

Yes sir All old date in DD/MM Format. we trying to change in Same as NewDate also DD/MM.

But when we are uploading on qlikview it will take fiv date as DD/MM and fiv date as MM/DD.

You can see the diff of both date

IT	15/05/2022	15/05/2022
Pharma	25/05/2022	25/05/2022
IT	6/6/2022	06/06/2022
Farmer	10/3/2022	03/10/2022

Thanks 

Sandip Chaudhari

[Sandip]

Yes it's Working sir,

but fev date showing null

Name	dept	date	NewDate	Payment	New
Kaya	Analystics	13/02/2022	13/02/2022	59694	13/02/2022
Pavan	Chemical	13/4/2022	13/04/2022	32599	13/04/2022
Saurabh	Analystics	25/04/2022	25/04/2022	46146	25/04/2022
Shubham	Pharma	30/04/2022	30/04/2022	38722	30/04/2022
Sandip	IT	15/05/2022	15/05/2022	38715	15/05/2022
Rahul	Pharma	25/05/2022	25/05/2022	26211	25/05/2022
Chetan	IT	6/6/2022	06/06/2022	42630
Kalu	Farmer	10/3/2022	03/10/2022	49255
Mahesh	IT	11/3/2022	03/11/2022	27317
Prasad	Banking	8/4/2022	04/08/2022	31212

And 

Sir if we are change format in one of the date like ,

25/05/2022 ---(DD/MM/YYYY)----Day-25 Month-05

After Converting format for same date Like --

05/25/2022---(MM/DD/YYYY)  -- Month-05 Day 25 

above eg no Change the day(25) and month (05) after changing the format.

But if we see  in another date cell like 

Rahul	Pharma	25/05/2022	25/05/2022	26211	25/05/2022
Chetan	IT	6/6/2022	06/06/2022	42630
Kalu	Farmer	10/3/2022	03/10/2022	49255
Mahesh	IT	11/3/2022	03/11/2022	27317
Prasad	Banking	8/4/2022	04/08/2022	31212

for eg . 10/03/2022 format is DD/MM/YYYY as we put in excel.

her Day - 10 and Moth 03 

But when we change format for the same for all that time  last four date will be change but wrongly.

eg. 03/10/2022 Day - 03 and Moth 10 (but it's wrong )

so this change we are trying to change .

Thank You for the support 

Thanks 

Sandip chaudhari

[salonicdk28]

Can you try in front end in the table dimension itself , it should work there and in the script editor we have formats defined in the main page so it is creating an issue there in back end

=Date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY')

Hope you get the scenario

Thanks,

Saloni (she/her/hers)

[Sandip]

Not Working as front as back end 

Thank You for support

Thanks 

Sandip chaudhari