Date format as in integer? How to convert?

Hi,

I’ve exported a MySQL database to a CSV file, but the date is a 10 digit integer, such as 1068328201. I’d like to convert this to a date format in Excel. Any ideas on how to convert it to mm/dd/yy?

Thanks.
David.

how did you export the data?

Thanks for the quick reply. I used the phpMyAdmin function of Cpanel and selected export type “CSV for Excel” to export the table. I also tried “Excel 2000” format but the result is the same - dates as a 10-digit Integer. Another example: 1149754124.

that’s because the table you are exporting already has the dates stored as integers. you could find an excel function on the internet to convert a unix timestamp to an excel timestamp, or you could write a SQL statement that formats the column using the from_datetime() function, then click on the export link on the results page.

OK so this 10 digit integer is a Unix timestamp? I’ll see if I can find an Excel routine to do the conversion. Thanks for the hint.

why don’t you export a query instead of exporting the table – that way you can make the conversion easily

Got it. The formula is as follows, where A1 contains the Unix date:

=DATE(1970,1,1) + A1/86400