Converting Julian date to "mm/dd/yyyy" format.
Article ID: KB0081928
Updated On:
| Products | Versions |
|---|---|
| Spotfire Analyst | All Versions |
Description
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.
Issue/Introduction
How to convert Julian date to "mm/dd/yyyy" format.
Environment
Product: TIBCO Spotfire (desktop client / Analyst / Professional)
Version: All Supported Versions
OS: All Supported Operating Systems
Resolution
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
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
Additional Information
Custom Expressions
Feedback
Yes
No
Powered by
