🌱 Numbers.app and UNIX Epoch

Source

Barry
Re: UNIX Epoch time
May 12, 2012 11:55 PM (in response to _Michael_)

Hi Michael,

Unix Epoch time is defined as the number of seconds since midnight at the start of January 1, 1970.
 
Conversion to a 'standard date and time' is simply a matter of adding the UNIX Epoch time, as a Duration of that many seconds, to January 1, 1970 00:00:00. Here's an example, followed by the formula(s).

        B2: =NOW()

(Formatted as Date and Time, Date: Jan 5, 2009, Time: 19:08:09)

        C2: =DUR2SECONDS(B2-DATE(1970,1,1))

(This is Jerry's formula for conversion TO Epoch time, as given in Convert date/time to Unix epoch)

        D2: =j      

(The leading commas in the arguments for DURATION are necessary to establish that the value in C2 is read as a number of seconds. The easiest way to construct this function is to insert it from the Function Browser, place the cell reference to C2 in the 'seconds' location, then delete all of the other placeholder units, leaving the commas in place. The comma following the seocnds place may be deleted.)


Regards,
Barry

Trim string

    =MID(CELL, NUMBER, LEN(CELL))

convert hours to decimal

=TIMEVALUE(B9)×24

app spreadsheet