Worksheet calculations involving date and time data generate a lot of questions to our Tech Support department. Origin can detect some basic date format during import but in many cases data are imported as text instead of date. If data is left-aligned in a column, it’s treated as text, such as the 1st column in worksheet below. But Origin provides a collection of built-in string and date functions to convert text strings to date and do calculations for further analysis and graphing.

In this blog post we will:

- parse a date string
- convert string to date and display it as date
- perform a simple calculation with date data

If you would like to work through this with me, download this zip file

Please note: this blog is written before Origin 2017 is released so it used our traditional col(A) syntax to refer to column A in formula. Origin 2017 introduced new spreadsheet cell notation to directly using column short name A in formula. Please see **Origin2017_new_notation.ogw** in the zip file to see how easy it is in Origin 2017.

If you have Origin 2016 or earlier, please open **scv_date_range_str.ogw** in Origin to follow the steps below.

This is what you should see:

The values in column A show a range of dates separated by the hyphen (“-“). Though they look like date, they are actually being treated as text string. So you can’t direcly get the differences of days in that data range. We need to first do some manipulating of the string in column A to extract the two dates separated by the hyphen (“-“).

- From the menu, choose
**Column: Add New Columns**and add 2 more columns. You should now have 4 columns. - Next, Enter “From Date” in column B’s long name. Enter formula in F(x) cell of column B to get the From dates of data range.

1 |
Left(col(A), Find(col(A),"-")) |

Looking closely at this simple line of code, you will see that we have called two string functions, **Find** and **Left**:

- The
**Find**function searches a string for a second string and returns the position from the first character of the found string. We search the string in column A for the hyphen (“-“) character and the function returns an integer value*N*equaling the number of characters up-to-and-including the hyphen. - The
**Left**function takes the*N*value returned by**Find()**and returns the left-most*N*characters from our date string. In other words, what is returned by**Find()**is treated as an argument of**Left()**, which in turn, returns its own value.

You can find more string functions from Origin’s extensive library of built-in functions

At this point, we notice we have a slight problem in that our expression returns the “-” character at the end of first half of our date string. We can fix that by modifying the **Find()** portion of our expression to *subtract* **1** from the value returned by **Find()**. Thus, our expression becomes …

1 |
Left(col(A), Find(col(A),"-")-1) |

… and we now find that the first portion of our date string has been extracted cleanly.

To extract the second portion of our date string, add column C and do something similar:

- Click in the
**F(x)=**cell of column C and enter the following:

1 |
Mid(col(A), Find(col(A),"-")+1) |

This time we have replaced the **Left** function with the **Mid** function:

- The
**Mid**function takes a string and returns*M*characters starting from position*N*in the string. The argument*M*is optional and if*M*is not specified,**Mid()**returns everything to the end of the string. This is precisely what we would like to do, so we do not specify*M*. - The
**Find**function, as before, is used to find the position of the hyphen (“-“) but this time, we need to start our count not from the hyphen, but from the next character (the actual starting point of the second date in our date string), so we*add***1**to the*N*returned by**Find()**and pass this value to**Mid()**.

Our worksheet should now look like this:

Column B and C are still being treated as text strings (left-aligned). To rectify this, enter col(C)-col(B) in F(x) cell of column D. It failed to calculated differences of days between dates in column C and B.

We can use **Date** function in Origin to convert a date-time string and returns a Julian-date value, used to perform mathematical operations on date and time data. See the updated formula and calculated differences of days in column D. Also note values in column B and C are right aligned to indicate they are not strings.

To show the correct display of Dates in column B and C, select column B and C header to select both. Choose **Format: Column…** to open the **Column Properties** dialog. On **Properties** tab, change **Format** to **Date**. Default Display is **MM/dd/yyyy** (this actually displays as today’s date in your interface). You can choose other display if needed. Click OK, you will see

We could end our discussion here but it is worth pointing out that there is a more elegant solution to this problem. Since our goal was simply to return the difference in days between two dates, we could have combined all of our parsing and math operations into a single column formula and saved ourselves a couple of steps. Let’s see how this is done:

- Go to the
**File**menu and choose**Recent Books**and the file scv_date_range_str.ogw should there at the top of the list. - Click the file name and a new instance of that window is opened.
- In column B, in the
**F(x)=**cell, enter the following formula:

1 2 |
date(Mid(col(a),find(col(a),"-")+1)) - date(Left(col(a),find(col(a),"-")-1)) |

Exit editing mode by clicking outside the cell and you will see that the same operation that we labored over earlier has been performed on the date string in column A, using just a single formula. How is this possible?

If you look at the formula that you entered into column B, you should recognize elements of the formulas that we constructed earlier to parse and subtract our dates, but with a couple of differences:

- We have nested the formulas that we arrived at earlier, using
**Find()**,**Left()**, and**Mid()**, inside another function — the**Date**function. - If your string isn’t in format of
**“M/d/yyyy”**, then you will need to specify the format in**date**() function. E.g.

1date(Thu Sep 24 09,"ddd MMM dd yy")

So there you have it: Using Origin’s **Set Values** feature and its extensive library of built-in functions, we were able to extract dates from a column of text strings, convert those extracted strings to Julian-date values and subtract the Julian dates to give us the number of days that had elapsed between two dates.

That’s about all we have time for. If you are interested in learning more about the primary features discussed in this post, see the following topics in our online documentation center:

- Setting Column Values in the Worksheet
- LabTalk Supported Functions by Category
- Dates and Times in the Origin Worksheet

Thanks for your time. I hope this has been helpful!

## Leave a Reply