How to convert UNIX datetime value to date/time string in OpenOffice Calc or Excel

To convert a UNIX datetime value to date/time string in OpenOffice Calc or Excel use this formula (assumes your datetime stamp is in the A1 cell of the current spreadsheet):

=A1/86400+DATEVALUE("1/1/1970")

If you want to test the above you may use the following simple C program:
=======

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>

int main(int argc, char **argv)
{
    time_t  time_t_var;

    if (argc > 1)
    {
        time_t_var = atoi(argv[1]);
    }
    else
    {
        time_t_var = time((time_t *) NULL);
    }

    printf("UNIX time %ld is: %s (ctime)\n",
            time_t_var, ctime( &time_t_var ) );

    struct tm tm_val;
    gmtime_r( &time_t_var, &tm_val );
    printf("UNIX time %ld is: %s (gmtime)\n",
           time_t_var, asctime(&tm_val) );

    return 0;
}

=======
If you don’t know what to do with the above then this article is probably not for you but…oh well I’ll try to help you, you poor lost soul 🙂 Assuming you do work with a Linux machine…. Open a text editor (not Word or Office writer! something like mousepad or Kate). Copy and paste code provided above and save the file, e.g.  name it timestamp-test.c. Now go to the folder where you put it and type: gcc -o timestamp-test timestamp-test.c. You now have a little app in this dir called timestamp-test – execute it in the command shell/terminal and it will tell you the time now and what its timestamp value looks like. You may also give it a timestamp value on the command like and it will tell you what date/time it corresponds too.

Here’s 2 examples:

1. No params (shows what time is it now):

$ ./timestamp-test 
UNIX time 1364822865 is: Mon Apr  1 16:27:45 2013  <-- date/time based on your locale
 (ctime)
UNIX time 1364822865 is: Mon Apr  1 13:27:45 2013  <-- the time as GMT 
 (gmtime)

2. With a UNIX timestamp as a param:

$ ./timestamp-test 1197055498
UNIX time 1197055498 is: Fri Dec  7 21:24:58 2007
 (ctime)
UNIX time 1197055498 is: Fri Dec  7 19:24:58 2007
 (gmtime)

Now you can paste the timestamps given above into Excel or OO and verify that the formula provided gives you the same date/time string as above…