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

[jovendeluna21]

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!

[mussaenda]

Hi @jovendeluna21,

Looks like Unix Timestamp.

Have you solved your issue?

@MattAllington provided a solution with a sample pbix file attached.

[MattAllington · Accepted solution]

Sample File

https://www.dropbox.com/s/2mqbra1cuhtp0ga/date%20time%20conversion.xlsx?dl=1

[Anonymous]

In the dialog box, copy and past this EXACTLY:

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

[Anonymous]

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]

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]

Sorry, that's wrong. It's:

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

--Nate

[MattAllington]

Sounds like unix date format. https://exceljet.net/formula/convert-unix-time-stamp-to-excel-date