2007-05-11
Calculating date and time from Unix time in Excel
A while back I had a problem where I needed to convert Unix timestamps in MS Excel to proper date and time fields. Well, I experimented and came up with a simple solution:
here is what I discovered:
* Excel stores the date as an integer number with a starting point of 1900-01-01
* Unix time start on 1970-01-01
So I calculated the difference between the two, and came up with 25569.
Time is calculated as a fraction from date. Since I live in South Africa (GMT+2), I need to also add two hours, or 0.08333
Assuming the Unix time stamp is in column A (A1, A2 etc), my formula now looks like this:
here is what I discovered:
* Excel stores the date as an integer number with a starting point of 1900-01-01
* Unix time start on 1970-01-01
So I calculated the difference between the two, and came up with 25569.
Time is calculated as a fraction from date. Since I live in South Africa (GMT+2), I need to also add two hours, or 0.08333
Assuming the Unix time stamp is in column A (A1, A2 etc), my formula now looks like this:
Code:
=(A1/86400)+25569.08333
