topic Re: Different Date Formats in QlikView

Different Date Formats

Tue, 11 Oct 2016 07:58:57 GMT

I have a QV Model that is extracting data from various excel spreadsheets. I have a field called "Claim Date" , where the date format is is not consistent in these excel spreadsheets. How do i create a formula in a list box to show a consistent date format as'DD-MMM-YY". (see below).

Kind regards

Nayan

Claim Date	Ideal Claim Date Format
2013-07-17	17-Jul-13
2013-07-16	16-Jul-13
2013-07-09	09-Jul-13
2013-07-07	07-Jul-13
2013-07-06	06-Jul-13
42649.21369	06-Oct-16
42644.12384	01-Oct-16
42644.12383	01-Oct-16
42642.40554	29-Sep-16
42642.40544	29-Sep-16
42641.27427	28-Sep-16
42641.27421	28-Sep-16
42640.21694	27-Sep-16
42633	20-Sep-16
42632	19-Sep-16
42631	18-Sep-16
42630	17-Sep-16
42629	16-Sep-16
42205	20-Jul-15
42152	28-May-15
42137	13-May-15
42124	30-Apr-15
42114	20-Apr-15

Re: Different Date Formats

lakshmikandh — Tue, 11 Oct 2016 08:15:24 GMT

Try Date & Date# functions

https://help.qlik.com/en-US/sense/1.1/Subsystems/Hub/Content/Scripting/InterpretationFunctions/date_hash.htm

https://help.qlik.com/en-US/sense/1.1/Subsystems/Hub/Content/Scripting/DateTimeInterpretation.htm

Re: Different Date Formats

antoniotiman — Tue, 11 Oct 2016 08:22:24 GMT

Hi,

try

Alt(Date([Claim Date],'DD-MMM-YYYY'),Date(Date#([Claim Date],'YYYY-MM-DD'),'DD-MMM-YYYY'))

Regards,

Antonio

Re: Different Date Formats

Tue, 11 Oct 2016 08:33:33 GMT

Thank you Antonio

Re: Different Date Formats

marcus_sommer — Tue, 11 Oct 2016 08:37:01 GMT

A small addition to the field-values which are a timestamp and not a date:

Alt(Date(floor([Claim Date]),'DD-MMM-YYYY'),Date(Date#([Claim Date],'YYYY-MM-DD'),'DD-MMM-YYYY'))

- Marcus

Re: Different Date Formats

Tue, 11 Oct 2016 08:47:37 GMT

Hi Marcus

Thank you.

Kind regards

Nayan