Converting Julian date to "mm/dd/yyyy" format.

Converting Julian date to "mm/dd/yyyy" format.

For this example, the Julian dates are in the column [StartDate] with one of the [StartDate] values being 202360. This article walks through how to convert this value to mm/dd/yyyy format using calculated columns. 1). Place the value of "StartDate" in a temporary variable beside the century. Use the following calculated column.
temp : Integer([StartDate] % 100000) 
Example: 202360%100000 = 2360

2). Extract the century from the Julian date using the following calculated column.
Century :Integer([StartDate] / 100000)
Example: Integer(202360/100000) = 2

3). Extract the exact year from the Julian date using the following calculated columns.
CenturyYear: Integer([temp] / 1000)
Example: 2360/1000 = 2

CenturyYear1 :
Case: When [Century = 2] 
            Then 
                CenturyYear1 = Integer([century]*1000)
            Else
                CenturyYear1=Integer([century] * 1900)
             End
Example: 2*1000 = 2000

Year1:  [centuryYear1]+[centuryYear] 
Example: 2000+2 = 2002

4). To Calculate Month, the following calculated columns are used.
Days: [temp]%1000 
Example:This calculated column will generate value "360"

Month: case  WHEN [Days]<=31 THEN 1 
WHEN [Days]<=60 then 2 
WHEN [Days]<=91 then 3 
WHEN [Days]<=121 then 4 
WHEN [Days]<=152 then 5 
WHEN [Days]<=182 then 6 
WHEN [Days]<=213 then 7 
WHEN [Days]<=244 then 8 
WHEN [Days]<=274 then 9 
WHEN [Days]<=305 then 10 
WHEN [Days]<=335 then 11 else 12
end                  

Example:This calculated column will generate the value "12".

5). To calculate the actual day,  use the following calculated column.
ActualDay: case  WHEN [Days]<=31 THEN [Days] 
WHEN [Days]<=60 then [Days] - 31 
WHEN [Days]<=91 then [Days] - 60 
WHEN [Days]<=121 then [Days] - 91 
WHEN [Days]<=152 then [Days] - 121 
WHEN [Days]<=182 then [Days] - 152 
WHEN [Days]<=213 then [Days] - 182 
WHEN [Days]<=244 then [Days] - 213 
WHEN [Days]<=274 then [Days] - 244 
WHEN [Days]<=305 then [Days] - 274 
WHEN [Days]<=335 then [Days] - 305 else [Days] - 335
end    
              
Example:This calculated column will generate the value "25".

6). From the above steps, Year, Month and day are obtained. Concatenate these to get the date in the required form using the following calculated column.
RequiredColumn : Date([Year1],[Month],[ActualDay]) 
Example:  Date([2002,12,25)  =   12/25/2002 Custom Expressions