topic Re: Change date format in script from US to UK format in QlikView

Change date format in script from US to UK format

Tue, 22 Nov 2011 09:19:43 GMT

Hello,

I am trying to change the format of a US based excel spreadsheet from US to UK (i.e. from MM/DD/YYYY to DD/MM/YYYY) in the script. Whilst I can do it in the spreadsheet it isn't feasible as the data is updated on a daily basis. I know I can simply do the following expression in the script;

Date(Date,'MM/DD/YYYY') as NewDate

but it doesn't really fix the problem as I want to create a monthyear field from this as well and it won't work. Does anyone have any suggestions how to fix this in the script or otherwise?

Thanks,

Ralph

Change date format in script from US to UK format

Tue, 22 Nov 2011 09:22:28 GMT

Hi,

Try it with:

makedate(right(Date,4),left(Date,2),mid(Date,4,2))

Succes!

Change date format in script from US to UK format

Tue, 22 Nov 2011 09:25:03 GMT

Hi Halmar,

Thanks for this, do you know how to make a month/year date from this as well, e.g. MMM/YYYY?

Many thanks,

Ralph

Change date format in script from US to UK format

Tue, 22 Nov 2011 09:29:51 GMT

Hi,

Try:

date(newdate,'MMM') & '/' & date(newdate,'YYYY')

Halmar

Re: Change date format in script from US to UK format

ToniKautto — Tue, 22 Nov 2011 09:32:38 GMT

I not sure I get exact where you run in to problems, so if the following is not helpful please provide more details on the problem sympthoms that you observe.

In genral it is always better to solve formatting issues in the script, since then you get simpler expressions in the application front end, and also only use process for formatting during reloads.

Keep in mind that when working with dual values like dates, you might need to run both Date() and Date#() in order to set both the numeric value and the textual presentation.

After you have formatted the date value properly, and want to use it for further operations a preceeding load setup is a easy way to rework or use the value. See my calendar application as an example of preceeding loads;

http://community.qlik.com/qlikviews/1022

Re: Change date format in script from US to UK format

ToniKautto — Tue, 22 Nov 2011 09:50:34 GMT

Calling the Date function twice seems a bit unecassary

(newdate,'MMM') & '/' & date(newdate,'YYYY')

try to make the formatting in one call instead

date(newdate,'MMM/YYYY')