Formula for converting a unix timestamp to a readable date?

Question

Formula for converting a unix timestamp to a readable date?

I don't see a formula which will take a unix timestamp integer (i.e the number of seconds since 1/1/1970) and convert that to a readable date. anyone done this before?

Posted on Feb 28, 2008 6:46 AM

Reply

Answer 1

pw1840

Level 3

860 points

Feb 28, 2008 7:29 AM in response to Moshe Weitzman

Moshe,

From your description of the unix timestamp, it seems that you just need to add the number of days represented by that number to Jan 1, 1970. Thus the formula as used in the demonstration below:

=B1+B2/24/60/60

pw

Reply

Answer 2

★ Helpful

pw1840

Level 3

860 points

Feb 28, 2008 11:53 AM in response to Moshe Weitzman

Moshe,

On further reflection, you can change the formula I gave you earlier to:

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

This eliminates the need for the 1/1/1970 in cell B1 plus combined division.

pw

Reply