Formatting a Mixed Datetime Field - Page 2 - Qlik Community

haymarketpaul · ‎2016-02-09

I've got a column with a mix of datetimes in it like this and i just want to format the column to leave me with DD/MM/YYYY

10/15/2015 10:16 AM

10/02/2015 22:24

09/25/2015 03:13 PM

10/06/2015 11:42

09/27/2015 12:26 PM

10/17/2015 09:03 PM

10/01/2015 15:15

10/18/2015 12:07 AM

10/06/2015 08:08

10/12/2015 15:13

10/09/2015 18:27

08/24/2015 12:23 PM

10/20/2015 10:12 AM

10/14/2015 01:10 PM

Any ideas much appreciated

haymarketpaul · ‎2016-02-09

That's close except the dates without the AMPM part are still in MM/DD/YYYY format...

TimeStamp(

Alt(

Num([Opt In Date]),

Num(TimeStamp#([Opt In Date], 'MM/DD/YYYY hh:mm TT')),

Num(TimeStamp#([Opt In Date], 'MM/DD/YYYY hh:mm'))

), 'DD/MM/YYYY')

ie...

10/01/2015

10/02/2015

10/06/2015

24/08/2015

10/09/2015

25/09/2015

27/09/2015

28/09/2015

29/09/2015

30/09/2015

10/10/2015

14/10/2015

15/10/2015

17/10/2015

18/10/2015

20/10/2015

22/10/2015

10/12/2015

Mark_Little · ‎2016-02-09

Strange formatting problem?

try

IF(RIGHT([Opt In Date],1) = 'M',

Date(Date#(Left(TRIM([Opt In Date]),10),'MM/DD/YYYY'),'DD/MM/YYYY'),

Date(Date(Left(TRIM([Opt In Date]),10),'MM/DD/YYYY'),'DD/MM/YYYY')) as DATE

haymarketpaul · ‎2016-02-09

No cigar i'm afraid...

10/01/2015

10/02/2015

10/06/2015