locked
Convert from Julian Date RRS feed

  • Question

  • Anyone know how to transform a 5 digit Julian date (from Db2 400) coming through as a number into Gregorian / regular date in Power Query?
    Wednesday, November 11, 2015 1:24 AM

Answers

  • Hello Jsaulez

    You mean YYDDD date format? Then something like this (Julian date in column JDate and has Int64 type):

    = Table.AddColumn(Source, "GDate", each #date(1900+Number.IntegerDivide([Jdate], 1000),1,1)+#duration(Number.Mod([Jdate], 1000)-1,0,0,0))


    Maxim Zelensky Excel Inside

    • Proposed as answer by Tristan St-Cyr Thursday, November 12, 2015 7:20 PM
    • Marked as answer by Jsaulez Friday, November 13, 2015 12:34 AM
    Wednesday, November 11, 2015 3:37 PM

All replies

  • Hello Jsaulez

    You mean YYDDD date format? Then something like this (Julian date in column JDate and has Int64 type):

    = Table.AddColumn(Source, "GDate", each #date(1900+Number.IntegerDivide([Jdate], 1000),1,1)+#duration(Number.Mod([Jdate], 1000)-1,0,0,0))


    Maxim Zelensky Excel Inside

    • Proposed as answer by Tristan St-Cyr Thursday, November 12, 2015 7:20 PM
    • Marked as answer by Jsaulez Friday, November 13, 2015 12:34 AM
    Wednesday, November 11, 2015 3:37 PM
  • Many Thanks Maxim!
    Friday, November 13, 2015 12:34 AM
  • Hi, we use a julian date also coming from our legacy system, but it is in the format if CYYDDD (century, year,  and day). so today would be 116241 (sept 1st, 2016) Anyone know how I can convert this?
    Thursday, September 1, 2016 12:57 PM
  • Hi, we use a julian date also coming from our legacy system, but it is in the format if CYYDDD (century, year,  and day). so today would be 116241 (sept 1st, 2016) Anyone know how I can convert this?

    Can you say what legacy system you're using? All online calculators and formulas that support the CYYDDD format calculate 116241 as August 28th, 2016, so it's probable that your system is using a proprietary CYYDDD format. This being the case, you can modify the duration portion of Maxim's formula as follows:

    #duration(Number.Mod([Jdate], 1000)+3,0,0,0))

    Thursday, September 1, 2016 5:08 PM