FormatDate WebIntelligence Function Error
I have 2 date objects (start date and end date).
I need to be able to replicate the DAYS360 function that is in EXCEL. I have SQL code that allows me to do this and have been able to create a number of variables in my WebIntelligence document in order to return the correct values.
The first steps/variables to replicate the DAYS360 function that I am using are:
Get the Day from the Start and the End Dates
Day_Of_Start_Date: FORMATDATE(startdate; "dd")
Day_Of_End_Date: FORMATDATE(enddate; "dd")
Now Set these to a Maximum of 30 (since the DAYS360 function assumes no more than 30 days in a month)
Set_Day_Of_Start_Date: If Day_Of_Start_Date = 31 Then 30 Else Day_Of_Start_Date
Set_Day_Of_End_Date: If Day_Of_End_Date = 31 Then 30 Else Day_Of_End_Date
Next, substract the Start Date day number from the End Date day number
End - Start = TONUMBER(Set_Day_Of_End_Date) - TONUMBER(Set_Day_Of_Start_Date)
Next, workout the months between the start date and the end date
Months = MONTHSBETWEEN(Start_Date; End Date)
Next, multiply the months value by 30
Finally calculate DAYS360 as
DAYS360 = [30*Months]+[End - Start]
All this works perfectly well when the browser locale is English, but when the browser locale is German an error #ERROR is returned in the WebIntelligence document. I cannot understand why.
I think that I am going to have to create the objects in the Universe rather than use variables in the WebI document. But I still want to understand why this does not work for a German Locale.
Note that both date formats (English locale and German locale are dd/mm/yyyy)
Please can you help.
Thanks in advance