Importing Time Data in Excess of 24 Hours

はじめに

Occasionally users have the need to work with time data with hours that exceed 24. Some examples of this include:

30:45 => 30 hours, 45 minutes

60:15:30 => 60 hours, 15 minutes, 30 seconds

For the purposes of this post, I will refer to this type of time data as cumulative time because time in excess of 24 hours tends not to refer to a particular time of day- generally it refers to a span of time. To utilize this type of time data in Origin, some special considerations have to be made.

Typically cumulative time data is based on times manually recorded by an experimenter or the output of an instrument. Moreover, this type of data is time-only; it is not associated with any particular calendar date or day of year.

In this blog post, I will show you how to import cumulative time data that exceeds 24 hours. I will also discuss issues with copying and pasting this kind of data, and lastly, I will show you how to convert it to fractional times. Graphing cumulative time data and/or using it in an analysis operation is straightforward, so I will not go into detail in this post. However, I have included graphing and analysis examples in the OPJ file included in a downloadable Zip file designed to accompany this post.

Note: If you need to deal with time data does not exceed 24 hours, OriginLab has an excellent video tutorial on the subject.

Background

Before I start, I need to provide a little background into the inner workings of time-only data in Origin. Regardless of how it is displayed, time-only data in Origin is stored as a floating-point number (a number with both an integer part and fraction part). The integer part of a time value represents number of days and the fraction part represents a fraction of a day (hours, minutes, seconds, etc). This number is not associated with any calendar date or time. Using the aforementioned example 60:15:30, Origin internally stores that as 2.51076. If it is confusing, consider that 60 hours is 2.5 days and those extra minutes and seconds just add a little to the fractional part.

For the data related to this post, I will the following Origin custom time format for the data: H:mm:ss where:

H = 24 hours without leading zeros

mm = minutes with leading zeros

ss = seconds with leading zeros

Note: To find more information about time formatting specifiers and time formats in general, start with this resource.

Now we can more on to our first exercise- importing cumulative time data.

Importing Data

Included in the zip file are two files that will be used to demonstrate importing. One is a tab-delimited ASCII data file entitled “Instrument.dat”. The other is the same data except in the form of an Excel file entitled “Instrument.xlsx”. The partial contents of the two files are illustrated below:

 

We can see that the first column of data in both files is in cumulative time format. In the ASCII file, these times are plain text, but within the Excel file, the data is automatically formatted by Excel using a variant of its own custom time format (which is somewhat different than that of Origin and not really germane to this discussion). If the data in the Excel column is aligned to the left, this text-format data, not time format data (within Excel that is). When importing the Excel file, Origin can handle that this difference as I will explain in the Importing the Excel File section below.

By the way- The second column in both files is simply arbitrary data that may be used for graphing and/or analysis.

 

Importing the ASCII File

(See the “1. ASCII Import” folder in the sample OPJ.)

Method 1: Drag and Drop

1. Drag and Drop Instrument.dat from Windows Explorer to Origin workspace.
2. It will create a new workbook with data imported. Origin detects the first column is time and set the column as Time.  By default it uses HH:mm format.
3. Double click the column header to open Column Properties dialog. Change Display to HH:mm:ss or choose Custom DIsplay and enter H:mm:ss if you don’t want leading zeros.

Method 2: ASCII Import

1. Create a new workbook.
2. select Data->Import From File->Single ASCII… menu. Select Instrument.dat to import.
3. Follow step 3 in Method 1 to change time display.

Note: If in step2 you check the Show Options Dialog checkbox, then you can go to Import Options->File Structure section, enter H:mm:ss for Custom Time Format. Then after import, time column will show with Custom Time format you said.

 

Method 3: Copy and Paste

1. Open Instrument.dat in Notepad. Select All and Copy.
2. Then go to a new workbook in Origin. Click first cell in col(A) and paste.
3. Right click the first row header which contains column name and choose Set as Long Name. This moves header info Cumulative Time 、 Results into the Long Name row.
4. Now the first column is left-aligned, which means it’s treated as Text. Double click column header to open Column Properties dialog. Set the Format as Time and change Display.

 

Importing the Excel File

(See the “2. Excel Import” folder in the sample OPJ.)

Method 1: Drag and Drop

1. Drag and Drop Instrument.xlsx from Windows Explorer to an empty portion of the Origin workspace.
2. Confirm Yes to import the Excel file into a new workbook. Note that time data are right-justified indicating that Origin converted the time into numbers.
3. Right-click the first row header which contains column name and choose Set as Long Name. It will bring Cumulative Time 、 Results into the Long Name row.
4. Double-click column header to open Column Properties dialog. Change Display to HH:mm:ss or choose Custom DIsplay and enter H:mm:ss if you don’t want leading zeros.

Method 2: Excel Import

The only advantage of this method over Method 1 is that you can specify 1st row of data to be used as Column Long Name during import so you don’t need to manually set it after import. Here are the steps:

1. In Origin, create a new workbook.
2. Select Data->Import From File-> Excel (XLS, XLSX, XLSM) menu.
3. In the File Open dialog, select Instrument.xlsx (be sure your Files of type filter is properly set) and make sure the Show Options dialog box is checked.
4. In the impExcel dialog, use all default settings except in the Header Lines section, set Number of Subheader Lines to <auto> and Long Names to 1.
5. Click the OK button.
6. Origin converts the time into numbers.
7. Double click column header to open Column Properties dialog. Change Display to HH:mm:ss or choose Custom DIsplay and enter H:mm:ss if you don’t want leading zeros.

 

There is no Method 3: Copy and Paste since it just doesn’t work well.

 

Converting Cumulative Time Data to Fractional Times

(See the “3. Conversion to Fractional Times” folder in the sample OPJ.)

I will now show you how you can express cumulative time data as fractional times (which are floating point values). In terms of worksheet columns, they will have Text & Numeric as the column format because they are numeric. These expressions of time include fractional days, hours, minutes, seconds and even years (I won’t bother with months because that gets complicated since months are based on periods of a calendar).

Using the Cumulative Time column A(X)  from the imported “Instrument.dat” data, we can convert cumulative time data into fractional times by using the following formulas in the  F(X)= label row:

Fractional Days: col(A)   (Column A is already a fractional day internally. so we just copy it without changing the column format from Time.)

Fractional Hours: col(A)*24   (Multiply the fractional day by 24 hours.)

Fractional Minutes: col(A)*24*60 (60 minutes in an hour.)

Fractional Seconds: col(A)*24*60*60 (60 seconds in a minute.)

Fractional Years: col(A)/365.25 (Divide fractional day by 365.25.)

An example of results is below:

 

Graphing and Analyzing Cumulative Time Data

Graphing and analyzing cumulative time data is pretty standard. See the “4. Graphing & Fitting” folder in the sample OPJ for an example of how to create a scatter plot and nonlinear fit with time as X.