4.5.2 Dates and Times in the Worksheet

WksCol-DateTime

As mentioned, all data in the worksheet is treated as either text or numeric. If data are left-aligned in the worksheet, they are being treated as text; if they are right-aligned, they are being treated as numeric.

It is important to understand these things:

• Date-time data that are left-aligned in the worksheet are being treated as text, despite appearing to be date-time data.
• Properly formatted date-time data will be right-aligned and are stored internally as numeric data of type Double(8) -- 64-bit, double-precision numbers. Any math operations on date-time data are performed using the internally-stored double-precision numbers.

Origin's Date-Time System

Origin interprets dates based on the Gregorian Calendar and supports a date range of 1/1/0100 to 12/31/9999. Origin's internal system for date-time is based on the astronomical Julian Day, which defines January 1, -4712 (January 1, 4713 BCE), 12 hours Greenwich Mean Time as "time zero" (t0). Origin's default time-keeping system differs in employing a 12 hour offset (see Origin's Default System: Adjusted Julian Date).

Beginning with Origin 2019, Origin gets two new date-time systems that use alternate "time zero" points. One is a "true Julian Date" system that drops the default system's 12 hour offset. The other is a "2018" system for those who deal in fractional seconds and need greater precision than the default system can provide. For more information, see Alternate Date-Time Systems in Origin.

Formatting Date and Time Data

Unless you are making use of Origin's available mechanisms for parsing date-time data (e.g. an import filter) you will likely find that your date-time data are treated as text during import. At this point, the procedure for applying proper date-time formatting to imported worksheet data goes something like this:

1. Open the Column Properties dialog box by (a) double-clicking on the column heading of your date-time data column, (b) selecting the column then right-clicking and choosing Properties or from the menu choosing Format: Column.
2. In the Properties dialog, click the Properties tab. Under Options, set the Format to Date or Time. Date formats can handle a time component; Time formats handle only days (IRIG), hours, minutes, seconds and fractional seconds. Further, there is a 548 day limit to Time formats (e.g. 13151:00 entered into a Time column set to Display HH:mm will display as 13151:00 but entering 13152:00 will display as 00:00).
3. Click the Display drop-down list and try to match your data to one of the listed date or time formats. For guidance, see Origin's Date and Time Format Specifiers, listed below.
4. If none of the built-in formats match the format of your date-time data, look toward the bottom of the list for two custom date formats Global Custom 1 or Global Custom 2 and try to find a match there.
5. If you fail to find a suitable match in the built-in or global custom Date or Time formats, set Display = Custom Display. Look for a matching custom date-time format (there is a pre-populated list containing some common custom formats); or directly enter a custom date-time string in the Custom Display box using Origin's Date and Time Format specifiers.

Note that you could preset the column to display date-time and then directly enter your date-time data using the keyboard. While there is some advantage in doing this -- for instance, if you set Format = Date and Display = the topmost built-in format (e.g. "10/2/2018") and begin direct keyboard entry, Origin will correctly parse "10/02/2018", "Oct 2, 2018" and "10-2-18" and display these in the chosen format -- most will find themselves having imported a data file and wanting to display the left-aligned (text) date-time data correctly as right-aligned, numeric data with a specific date-time display format.

When you define a Custom Display format in the column's Properties dialog box, the custom format is added to the Custom Date Format list in the ASCII Import (impASC) dialog box. Save this file structure option as part of your Theme and you can easily import new files without having to recreate your original settings.

Date & Time formats and their underlying numeric values

Once you have properly formatted your date-time string as date-time data (it is right-aligned in the worksheet cell indicating that it is recognized as numeric), you can freely switch between any of the available date-time display formats without any loss of precision. Recall that date-time values are stored internally as numerics of type Double(8) and that what appears in the worksheet is merely a representation of that underlying numeric value. Changing the display format from 9/19/2018 HH:mm:ss to Wed and back to 9/19/2018 HH:mm:ss, does not affect the underlying numeric value.

The following image was made by filling all worksheet columns with the same (numeric) Julian Day value, then setting a different formatting option for each column, using the Column Properties Display list.

A Note about Missing Values in Date-Time Data

If there are gaps in your date-time data, they may appear as missing values ("--"). You can opt not to display the double-dash by changing the value of LabTalk system variable @JM:

@JM = 0; // (default) display missing values as double-dash
@JM = 2; // display missing values as blank

For information on changing the value of a LabTalk system variable, see FAQ-708 How do I permanently change the value of a LabTalk system variable?

Origin's Date and Time Format Specifiers

The following table lists Origin's supported date-time format specifiers. Use these to interpret available built-in formats or to construct Custom Display formats.

Custom Date and Time Formats

Places where you can define custom date and time formats

• Define up to two custom date-time formats in Preferences: Options. These two custom formats will show near the bottom of the Column Properties Display list and are labeled as (Global Custom 1) and (Global Custom 2).
• Define any number of date formats directly in the Custom Display box that shows when you set Display = Custom Display on the Properties Tab of the Column Properties dialog box.
• Define custom date formats on the Data Columns Page of the Import Wizard dialog.

Custom date and time formats defined in Column Properties or in the Import Wizard will be saved in the project. In contrast, the two global custom date-time formats defined in Preferences: Options are locked to the Origin installation on which they were defined.

Other things to note about custom date and time formats

• Custom date strings are limited to no more than 60 characters.
• "Sandwich" format strings such as TIME-DATE-TIME or DATE-TIME-DATE are not allowed.
• Only the standard, regionally-defined '.' and ',' are supported as decimal point separators between seconds and fractional seconds.
• When you define a custom date format, it will list at the top of the Custom Display drop-down list. Custom date-time formats are saved in the project file. To share the format with other users without sharing your project file, go to your User Files Folder (Help: Open Folder: User Files Folder) and look for the Origin.ini. Open the file in a text editor and find the Column Custom Format List section. Copy the format and send it to your colleague. Your colleague can paste the customized format to the Column Custom Format List section of his/her User Files Folder/Origin.ini file and resave the .ini file.

Custom Dates Examples

When the structure of date-time data in your worksheet does not match one of Origin's built-in or available custom format options, you will need to define your own custom format. As mentioned in Formatting Date and Time Data, you can use Origin's Date and Time Format Specifiers to:

• Define a Global Custom format in Preferences: Options.
• Directly enter a custom date-time definition on the Data Columns page of the (ASCII) Import Wizard, with the option of creating an import filter file that, among other things, "remembers" your custom date-time format.
• Directly enter a custom date-time definition in the Custom Display box in Column Properties. Note that definitions entered here are saved to the Origin.ini file and will become permanent in the Custom Display list, unless otherwise removed by editing the [Column Custom Format List] of Origin.ini.

For instance, to create a custom date selection that displays the format ...

Saturday, September 5, 1998

.... you need a specifier for full day name (dddd), full month name (MMMM), day of the month (d) and full year (yyyy).

Hence, you would type:

dddd, MMMM d, yyyy

IRIG Time

The IRIG (Inter-Range Instrumentation Group) time format is used in government, military, and commercial fields. The most common version of the IRIG Time Format is IRIG-B, which sends day of year (0 - 365/366), hour, minute, seconds/fractional seconds data on a 1000 Hz carrier with an update rate of one second.

Origin supports import and display of IRIG time in the worksheet, by drag-and-drop, ASCII import (Data: Import from File: Import ASCII) or Import Wizard (Data: Import from File: Import Wizard). While your files with IRIG times should import as numeric data (no need to convert from text to numeric), you may want to adjust the Column Properties Display drop-down to display your data in a particular format. The following Display formats are reserved specifically for IRIG time, when Format = Time, where ...

• DDD = 3 digit day count

with the option of displaying the following components:

• DDD:HH - leading zero, 24 hour time
• DDD:HH:mm - leading zero, minutes
• DDD:HH:mm:ss - leading zero, seconds
• DDD:HH:mm:ss.## - fractional seconds, 2 decimal places
• DDD:HH:mm:ss:###### - fractional seconds, 6 decimal places

Origin supports custom display of IRIG time, as long as you use these format specifiers. In addition, you may display your IRIG time data in any of the standard (e.g. hh mm ss'.'##) or custom time formats built using the time format specifiers listed in the table above.