topic Re: Splitting columns in Discussions

Splitting columns

SMast — Fri, 07 Feb 2020 11:51:52 GMT

Hi,

i have a column full of dates with different time points in the date. Eg:

2020-01-31T11
2020-01-31T12
2020-01-31T13
2020-01-31T14
2020-01-31T15
2020-01-31T16
2020-01-31T17
2020-01-31T18
2020-01-31T19
2020-01-31T20
2020-01-31T21
2020-01-31T22
2020-01-31T23
2020-02-01T00
2020-02-01T01

but I want to split them into two separate columns: date and time. I'm a beginner so I'm still getting use to the software.

thanks!

Re: Splitting columns

dale_lehman — Fri, 07 Feb 2020 12:05:50 GMT

You can use the Left, Substring, and or Right functions (found in the Character group in the formula window) to create 2 new columns, one with the date and the other with the hour (I am assuming the last two digits are the hour and the T just stands for time). Then, make sure the date column yyyy-mo-day is formatted as a date. So, to get the date you could use Left(current column, 10) and for the hour you could use Right(current column, 2).

Re: Splitting columns

txnelson — Fri, 07 Feb 2020 17:57:17 GMT

I have attached you sample data table and have added 2 columns. The formula I used for the first column is:

Informat( Substr( :Column 1, 1, 10 ), "Y/m/d" )

It was a pretty straight forward conversion, because the substringed data matched an existing Informat().

The second column formula is:

Informat( Substr( :Column 1, 12, 2 ) || ":0", "hr:m" )

The format required needs to have minutes along with the hour, so I just had to add it to the string returned from the Substr() function

I hope this helps you with your journey into using JMP