2.92 FAQ-936 How do I Convert a Unix Timestamp to an Origin Date?
Last Update: 5/23/2018
A Unix timestamp is defined as "...the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC)." ()
A Julian date is defined as "a continuous count of days and fractions since noon Universal Time on January 1, 4713 BC (on the Julian calendar)." ()
Origin uses Julian dates to internally store date data. So in order to perform date-based actions on Unix timestamps in Origin (e.g displaying as a date-time), it is necessary to convert them to Julian dates.
The formula is simple:
JulianDate = (UnixTimestamp / 86400.0) + 2440587.0
To convert Unix timestamps stored in a worksheet column using Set Column Values, add a new column to the worksheet, and use the following formula (assuming the timestamps are in column A).
Pre-Origin 2017 or Spreadsheet Cell Notation off:
(col(A) / 86400.0) + 2440587.0
Origin 2017 and later with Spreadsheet Cell Notation on:
(A / 86400.0) + 2440587.0
|Note: The observant reader may notice that the formula provided generates a value that is off by 0.5 from those generated by third-party converters. This is intentional because, for reasons historical to the product, Origin's Julian date values have a 12 hour offset from the proper definition of a Julian date. Please see this page for more information.