Different Date Formats

[#1]

Hi

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

[lakshmikandh]

Try Date & Date# functions

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

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

[antoniotiman]

Hi,

try

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

Regards,

Antonio

[#4]

Thank you Antonio

[marcus_sommer · Accepted solution]

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

[#6]

Hi Marcus

Thank you.

Kind regards

Nayan