How to convert Julian Date to Normal date?

[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

101362

114063

110176

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?

My back-end data source is a MS SQL server 2008 based universe.

[nscheaffer · Accepted solution]

What date values are each of your examples equivalent to?

Noel

[nscheaffer · Accepted solution]

I am going to make an assumption that the first three digits of you Julian date are the number of years since 1900 and the last 3 digits represent the number of days within the year.

I created four variables...

My Date="110176"

Days Offset=ToNumber(Right([My Date];3))

Year Offset=ToNumber(Replace([My Date]; FormatNumber([Days Offset]; "00#"); ""))

Formatted Date=RelativeDate(ToDate("01/01/" + FormatNumber([Year Offset] + 1900; "####"); "mm/dd/yyyy"); [Days Offset] - 1)

All that results in this...

If my assumption about the format of your Julian date is incorrect you should be able to tweak these formulas to make them work for your situation.

Noel

[Former Member · Accepted solution]

Hi Noel,

Thanks for the reply.

Based on your response I am trying to create the 4 variables and I am assuming that "My Date" will have the Julian Date field within the formula.

However, when I try to create the second variable (Days Offset), I get an error message as seen below:

I even tried to convert My Date to a number but this second variable still gives me an error message.

[nscheaffer · Accepted solution]

You could set My Date to your dimension that contains your Julian date or just plug your dimension straight into the Days Offset variable. In order to use the "Right" function the data type needs to be a string. It sounds like it is likely a number so you will have convert you Julian date dimension to a string using the "FormatNumber" function.

Noel

[Former Member · Accepted solution]

Thanks a lot Noel, All these steps were worked well!