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)." ([1])

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)." ([2])

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.