Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

FormatDate WebIntelligence Function Error

Hello

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

30*Months

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

Julian

Former Member
Not what you were looking for? View more on this topic or Ask a question