Forum Discussion

jovendeluna21's avatar
jovendeluna21
Icon for Helper IV rankHelper IV
5 years ago
Solved

Issue with converting a 10 digit date whole number date field into DD/MM/YYYY format from Web

I have a dataset taken from the web which stores dates in the form of 10 digit serial number and I am really struggling to convert it into a date format of DD/MM/YYYY. Formula such as below doesn't work for me. I'm receiving error.

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [last_status_change_date])

 

Hopefully anyone can help me on this.

Thank you!

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    If your Unix date is a whole number, then this will work:

    Table.AddColumn(PriorStep, "NewDate", each Date.From([UnixDate] + Number.From(#date(1970, 1, 1))), type date)

    --Nate

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Sorry, that's wrong. It's:

       

      Table.AddColumn(PriorStep, "NewDate", each Date.From([UnixTime]/86400)+#date(1970,1,1), type date)

       

      --Nate

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hey there. "PriorStep" just refers to whatever the name of your prior step (I'm sure the step is not actually names "PriorStep"). So if you are choosing to use the Add Column dialog box (which is the right thing to do!) then you do not need to begin with "Table.AddColumn". Just click the "Add Custom Column" button, then in the dialog box, type: each Date.From([UnixTime]/86400)+#date(1970,1,1), type date)

     

    --Nate

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    In the dialog box, copy and past this EXACTLY:

     

    each Date.From([last_status_change_date]/86400)+#date(1970,1,1)