How to convert Julian date(113113) to calendar date in webi reports?

[Former Member]

Hi All,

I am using Webi 4.1 and I have a problem where one of the columns in my report has a Julian Date format. The data is as seen below:

Date

113113

I need to be able to display all of this data into Normal date form as dd/mm/yy.

Is there anyway we can do this in Webi?

Also i have applied the below formula for the conversion.

My Date = FormatNumber([Recall for Review Date];"###")

Days Offset = ToNumber(Right([My Date( Recall for Review date)];3))

Years Offset = ToNumber(Replace([My Date( Recall for Review date)]; FormatNumber([Days Offset (Recall for review date)]; "00#"); ""))

Formated Date = RelativeDate(ToDate("01/01/" + FormatNumber([Year Offset(Recall for Review date)]+ 1900; "####"); "mm/dd/yyyy"); [Days Offset (Recall for review date)] - 1)

Here recall for review date is the dimension containing Julian values.

This formula giving me the date values as expected but for some entries like 113113 and 101101 it is showing the #error for the date field. could you please suggest is there any mistake i am doing in the formula or any other thing went wrong.

Kindly help.