Skip to Content
avatar image
Former Member

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Jan 05, 2012 at 10:38 AM

    Hi Julian,

    This is the behavior of the product; The #ERROR usually indicates an incorrect format entered in the formula to the format which is expected according to the locale used.

    The date format might be same for English and German locale but the date seperators are different, loke for english it is '/' and for German it is '.'

    Regards,

    Nakul Mehta

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      There are few settings in your webi Preference Tab which we need to take care.

      Just check what preference you have under

      Web intelligent > When viewing a Document

      Use the document locale to format the data

      - If this is selected than what ever the Local setting for the Document creation system it will come

      Use my preferred viewing locale to format the data

      You can view data as per your local setting

      Idly it should be u201CUse the document locale to format the datau201D.

      Try this and see if this can help you out.