US and UK date formats

[Anonymous]

Hi

I am loading data from two seperate data spreadsheets with date columns I need to link together.

Both are in Excel English UK short date format, but one of them is coming out as 04/01/2015 instead of 01/04/2015 meaning it is 4th January 2015 instead of 1st April 2015.

Is there loading script in Qlikview that can switch it from 04/01/2015 to 01/04/2015 so both of my date fields will link correctly?

[quwok]

When loading the spreadsheets, you can use date#(UKDateField, 'DD/MM/YYYY') and date#(USDateField, 'MM/DD/YYYY') in the load script to interpret the dates accordingly into QlikView.

[Gysbert_Wassenaar]

Check first if the values in excel are dates or string values. Simply change the format in excel to Number to check this. If the values are numbers you don't have to do anything in Qlikview other than set the date format to what you want it to be. If the values in excel are string values then you'll have to use the date# function to turn them into dates:

LOAD date(date#(MyDate, 'MM/DD/YYYY', 'DD/MM/YYYY') as MyDate

FROM ...