Julian Time Conversion

[Anonymous]

My data is imported from JDE. I have 2 separate columns for Date and Time. 

Eg:

Date: 121195

Time: 224012

How can I convert Time from Julian format to HH:MM:SS format? I want it as a separate column rather than a measure.

[v-yetao1-msft]

Hi @Anonymous 

I have a question to confirm . What does 121195 represent ? What does 224012 represent ? 

If the time format you want , what should be this time according to the data you provided ? You need to provide specific calculation logic so that we can provide the corresponding formula.

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Data imported from JDE shows Date and Time in Julian format as separate columns

For Example:

I have the date as 119200 and I have another column with time stored as 161140.  I have managed to convert the date and I've got 19 July 2019 after conversion.

I used Power Query to get the Date:

#"Calendar Date Updated" = Table.AddColumn(#"Calendar Promised Ship", "Date Updated Cal", each #date(1900+Number.IntegerDivide([Date Updated], 1000),1,1)+#duration(Number.Mod([Date Updated], 1000)-1,0,0,0))

I'm not sure about how I can get time to be converted from the Julian format of 161140 to standard HH:MM:SS. I don't know what Julian time 161140 corresponds to in standard format.

[tamerj1]

Hi @Anonymous 

you may try

CONVERT ( TableName[Date], DATETIME ) + CONVERT ( TableName[Time], DATETIME )

[Anonymous]

That didn't work, unfortunately.

What I want is to fill the '?'. But what I'm getting is this Calculated Datetime.