Easiest way to export CSV and view in Excel

Well, It’s fairly easy when someone has done it before you and you just google the answer, which is what I did :stuck_out_tongue_winking_eye:

In English the formula would be:

=(((A1/1000)-(7*3600))/86400)+(DATEVALUE("1970-01-01") - DATEVALUE("1900-01-01"))

To display the converted time in a more readable format you can choose Custom formatting and adjust the way that you want the data to appear:

image

If you go for dd/mm/yyyy hh:mm then it’s easier to read (unless you’re American and insist on totally messing-up the way that dates are written). Going for Year Month Date as @Distans has done means that you can sort the dates into chronological order much more easily.

Edited to add…
The UNIX times in CSV exports are in GMT/UTC timezone format. The formula above contains a calculation which deducts 7 hours from this to convert it to MST/PDT.

If you’re in Europe then you’ll need to add one hour (in winter) or 2 hours (in summer) so the formula for European SummerTime would be:

=(((A1/1000)+(2*3600))/86400)+(DATEVALUE("1970-01-01") - DATEVALUE("1900-01-01"))

Pete.

2 Likes