Convert date/time to Unix epoch

Question

Level 1

0 points

Convert date/time to Unix epoch

I have thousands of date time stamps I would like to convert to Unix epoch ten-digit strings. That is, take some thing like:

Dec 9, 2005 10:24 AM

and convert to

1234567890 (only in the correct unix epoch time)

What formula can I use? I've found that this "=DATE(1970,1,1)+P2/86400" can convert a 10 string epoch time down to just a date correctly, but without time. But I need to go in the opposite direction, with the time included.

Using Numbers 09 ver 2.0.5 (368)

Mac OS X (10.6.7)

Posted on Apr 20, 2011 8:04 AM

Reply

Answer 1

Top-ranking reply

Jerrold Green1

Level 7

30,247 points

Apr 20, 2011 10:12 AM in response to cporter9

CP,

An up-to-date Unix Time would be provided by:

=DUR2SECONDS((NOW()-"1/1/1970"))

A somewhat more robust form would be:

=DUR2SECONDS(NOW()-DATE(1970,1,1))

If the time to be converted is in Cell P2, the expression would be:

=DUR2SECONDS(P2-DATE(1970,1,1))

Jerry

Reply