Change date format in script from US to UK format

[#1]

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

[#2]

Hi,

Try it with:

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

Succes!

[#3]

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

[#4 · Accepted solution]

Hi,

Try:

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

Halmar

[ToniKautto]

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

[ToniKautto]

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')