Converting date as int from MySQL to date in Excel

Question

So I have been tasked to migrate data from a MySQL DB to a SQL DB, I am exporting tables into CSV, but notice that the Dates in the tables in MySQL are INT and I need to convert these to a Date format. I do TextToColumns to split the columns and then want to convert to date format, from something like this 1446839632 to a proper date format dd-MM-yyyy

I have NO idea how to do this, how can this be done ?

EDIT

What I have tried:

Tried using the export function in MySQL WorkBench to a SQL file, but this fails due to the MySQL Server and WorkBench not being on the same versions.
I tried following help to fix the above issue, but non work/doesnt have the same options in the "Advanced Options" within the Data Export option

This is why I am now exporting the tables one by one to a CSV(right clicking on table, export, etc.)

Any help will be appreciated.

Thanks!

My best guess is that your date is in epoch format i.e. a number of seconds since 1st Jan 1970. https://www.epochconverter.com/. In excel: `"1/1/1970" + (1446839632 / 60 / 60 / 24)` — FXD, Mar 26 '19 at 11:06
these dates are **UNIX Timestamps** See [this question](https://stackoverflow.com/questions/6267564/convert-unix-timestamp-into-human-readable-date-using-mysql) for how to convert it into a date in mysql (before you extract the data to csv) — Lelio Faieta, Mar 26 '19 at 11:08
Ah awesome! FXD and Lelio your answers led me to getting the answer. FXD your answer is actually spot on, the only thing I needed to change in your answer was the "1/1/1970" to DATE(1970,1,1). So the Formula is: `=DATE(1970,1,1)+(1446839632/60/60/24)` - Please post your answer and this formula and I will mark as the answer — AxleWack, Mar 26 '19 at 11:24

score 0 · Answer 1 · answered Mar 26 '19 at 11:30

Are the values you have unix timestamps?

If yes:
According to https://www.unixtimestamp.com/index.php

Timestamp Converter
1446839632
Is equivalent to:
11/06/2015 @ 7:53pm (UTC)
2015-11-06T19:53:52+00:00 in ISO 8601
Fri, 06 Nov 2015 19:53:52 +0000 in RFC 822, 1036, 1123, 2822
Friday, 06-Nov-15 19:53:52 UTC in RFC 2822
2015-11-06T19:53:52+00:00 in RFC 3339

Option Explicit

Function ts2d(TimeStampValue As Long) As Date
    ts2d = Convert_Timestamp_to_Date(TimeStampValue)
End Function

Function d2ts(DateVal As Date) As Long
    d2ts = Convert_Date_to_Timestamp(DateVal)
End Function

Function Convert_Timestamp_to_Date(TimeStampValue As Long) As Date
    Dim Date_1_1_1970 As Date
    Dim Long_1_1_1970 As Long
    Dim DaysCount As Date

    Date_1_1_1970 = DateSerial(1970, 1, 1)
    'Long_1_1_1970 = 25569
    DaysCount = TimeStampValue / 86400
    Convert_Timestamp_to_Date = DaysCount + Date_1_1_1970
End Function

Function Convert_Date_to_Timestamp(DateVal As Date) As Long
    Dim Date_1_1_1970 As Date
    Dim Long_1_1_1970 As Long
    Dim DaysCount As Date

    Date_1_1_1970 = DateSerial(1970, 1, 1)
    'Long_1_1_1970 = 25569
    DaysCount = DateVal - Date_1_1_1970
    Convert_Date_to_Timestamp = CLng(DaysCount * 86400)
End Function

score 0 · Accepted Answer · answered Apr 02 '19 at 10:21

FXD - I have asked you to post the answer but you have not yet, so I am posting the answer to help others see it easier, if you see this, please post the answer and I will rectify the marked answer to yours:

=DATE(1970,1,1)+(1446839632/60/60/24)

1446839632 can just be replaced with the cell that contains this value.

Converting date as int from MySQL to date in Excel

2 Answers2