Converting date MySQL database

[webportal]

I've imported a MySQL database from a PHP based CMS.

The dates in the database are formatted as integers.

For example:

2016-01-29 20:02 corresponds to:

1454097752

And:

2015-11-09 16:45 corresponds to:

1447087522

Now, I have the whole data in Power Pivot and I need to work with readable dates and I have no idea how to get them from integers as above.

Does anyone know of a conversion function I could use?

Thank you so much for your help!

[ImkeF]

This seems to be seconds since 1970.

So you can convert by adding a custom column with this formula

([NumberField]/24/60/60)+25569

This should return a number that you can then convert to Datetime.

[webportal]

Hi,

It should be something like that, yes.

For 1481574249 which is 2016-12-12 20:24

The formula 

([NumberField]/24/60/60)+25569

Returns:

42716,8501014

And applying DATEVALUE to that number returns the error:

"Type mismatch"

PS: The field is set to Auto (Date) 

[ImkeF]

Hi,

I did this as a column-operation in the UI.

But if you want to do it via a formula, try Date.From(..) instead.

[webportal]

I'm using a calculated column in Power Pivot.

=DATEVALUE([Date Column]/24/60/60+25569)

[ImkeF]

Wrong forum 😉

You can also apply a column operation there: Home -> Format -> Data Type: Date

Or again in a formula:

FORMAT([Spalte2]; "General Date")