4.5.3 Date and Time Math Operations
Importing of date-time data is much improved for newer versions of Origin. Origin 2020b added improved date-time import for the Text/CSV Connector and Origin 2021 added improved handling of date-time when copy/pasting from MS Excel.
With regard to older, X-Function-based import, unless you are importing date-time data using a filter or by specifying a custom format in ASCII options, your data are likely to be treated as text (you can do a quick check by looking to see if the date-time data are left-aligned (text) or right-aligned (numeric), in the worksheet). In order to use your date-time data in math operations (e.g. calculations of elapsed time), you will need to make sure that they are properly formatted and, thus, being treated as numeric data.
Also, if you need to perform math operations on date-time data, be aware that there are a large number of date-time, string and math functions built into Origin for manipulation of date-time numerics and date-time strings.
By default, date-time in Origin (Format = Date, but data include a time component) are stored internally as Julian-day (double) values where the integer part of the value is days and the fractional part is seconds. In this system, assuming contemporary dates, resolution is limited to 0.0001 seconds when rounded at the fourth decimal digit. Beginning with Origin 2019, you can choose a "2018" system in which "time zero" is January 1, 2018 at 00:00. In this system, the integer part is smaller allowing for more resolution for the fractional part. In this "2018" system, measurements to 10-8 seconds can be supported.
Date, Time, Month, Day of the Week
Date and date-time values in Origin are stored internally as Astronomical Julian Day Numbers of data type double(8). It is important to remember that it is these internally-stored values are independent of any Display option that you might chosen for your date-time data. It is these Julian Day values that are used when performing math operations on date and time data.
The other thing to note is that you can freely switch back and forth between any supported date-time Display option without any loss of precision, because the internally stored value is unaffected by your choice of Display format (i.e. switching from a date-time value formatted to the fractional second to month and back to the original date-time value, makes no change to the underlying value -- there is no loss of precision).
Origin supports four basic Formats: Date, Time, Month and Day of the Week, and there are a number of supported Display options for each of these (e.g. Month can "Jan", "January" or "J" in addition to the month display options used in date notations).
For more information, see:
- The Column Properties dialog box Format options.
- The Column Properties dialog box Display options.
Date and time math operations: dates and times
If you have a worksheet where your column "A" contains some date-time values and your column "B" contains some measurement made at each date-time value in column "A", you might want to know how much time had elapsed between each measurement. You can easily determine this by creating a third column "C", then use the Set Values dialog box to generate a dataset of elapsed time between measurements.
A[i]-A[i-1] //for i = 2 to last row of column A
Origin 2021b increased the limitation of Time formats from 240 to 548 days (i.e. you can now enter 13151:00 into a Time column set to Display = HH:mm and it will display as 13151:00, but entering 13152:00 will display as 00:00). When doing elapsed time calculations involving the passage of 548+ days, changing column Format from Time to Numeric will allow you to track time in days, including any fractional component.
Date and time math operations: months
As mentioned, there are a number of Origin functions that can be used to manipulate numeric and date-time text strings. A listing of these functions is found in the LabTalk Scripting Guide. When converting numeric values to month representations, Origin uses one of these functions - the mod (modulus) function.
Months are enumerated from January (1) to December (12). Numbers greater than 12 are handled by a modulus function (see Mod) when converted to Month. The LabTalk modulus function uses the following syntax:
mod(x,y) = the remainder from division of integer x divided by integer y.
46 translates to October because mod(46,12) is 10, and 10 is October (December = 0).
Note that you cannot type values outside the range 1 to 12 into a cell in a column in which Format has been set to Month. If you do so, Origin displays a missing value in the cell. To type such numbers into the worksheet, set the Format to Text & Numeric and enter your numbers. Then, you can return the column Format to Month.
Date and time math operations: days
Day of Week is enumerated from Sunday (0) to Saturday (6). Numbers greater than 6 are handled by a modulus function when converted to Day.
46 translates to Thursday because mod(46,7) is 4, and 4 is Thursday.
Note that you cannot type values outside the range 0 to 6 into a cell in a column in which Format has been set to Day of Week. If you do so, Origin displays a missing value in the cell. To type such numbers into the worksheet, set the Format to Text & Numeric and enter your numbers. Then, you can return the column Format to Day of Week.