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…
One Response to How to convert UNIX datetime value to date/time string in OpenOffice Calc or Excel
Leave a Reply Cancel reply
-
Categories
- Android Development
- Bash
- C programming
- dpkg/apt-get
- drupal
- Emacs
- Git
- Java
- Linux administration
- Linux device drivers
- Linux Gaming
- Linux kernel
- Linux Networking
- Linux on Windows
- Linux printing
- Linux sound and ALSA
- Package Managers
- Programming
- RPM
- Shell and environment
- Tips and tricks
- Uncategorized
- VirtualBox
- Virtualization
- web development
- wine
- WMaker
- Wordpress Tips
- X Window System
- XFCE
-
Articles
- August 2020
- August 2019
- May 2019
- July 2017
- February 2017
- January 2017
- November 2016
- October 2016
- September 2016
- August 2016
- July 2016
- June 2016
- April 2016
- March 2016
- December 2015
- November 2015
- September 2015
- June 2015
- May 2015
- April 2015
- March 2015
- February 2015
- January 2015
- December 2014
- October 2014
- February 2014
- January 2014
- November 2013
- October 2013
- June 2013
- April 2013
- March 2013
- February 2013
- January 2013
- December 2012
- October 2012
- August 2012
- July 2012
- June 2012
- May 2012
- April 2012
- March 2012
- February 2012
- September 2011
- August 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
-
Meta
Cloud
audio bash boot compile C programming cups drupal emacs etc Fedora git grep how to httpd init kernel libc linux linux partition localtime login make mount mp3 mysql networking oracle package managers password phpMyAdmin programming rpm shell sql vbox version control system virtual box vm web server wordpress www xargs xfce xwin yum
… and this post tells you how to convert to-from UNIX time in the shell … 🙂