Subscribe Bookmark RSS Feed

help with jsl to convert table column from datetime yyyy/mm/dd hh:mm:ss to work week?

rrtx_mike

Community Trekker

Joined:

Mar 18, 2016

Hi All,

I am looking for help to convert a table column from date time to work week, can you help me with a script to accomplish this conversion?

My data format is: 2016/03/21 8:59:27.921 PM and I'd like to convert this to calendar work week. I am using JMP 12.

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Use the JMP function:  Week of Year();

You will either have to create a new column with the above function as the formula for the column, or to replace the values within the same column the following script will do it:   Assume that the target column is named......MyDate

Names Default to Here(1);

dt=current data table();

For Each Row(

     MyDate=Week of Year(MyDate);

);

dt:MyDate << format("Best",5);

Jim
2 REPLIES
Solution

Use the JMP function:  Week of Year();

You will either have to create a new column with the above function as the formula for the column, or to replace the values within the same column the following script will do it:   Assume that the target column is named......MyDate

Names Default to Here(1);

dt=current data table();

For Each Row(

     MyDate=Week of Year(MyDate);

);

dt:MyDate << format("Best",5);

Jim
rrtx_mike

Community Trekker

Joined:

Mar 18, 2016

Thanks Jim you nailed it,

Here's the solution:

dt5 << New Column( "Work Week", Numeric, "Continuous", Formula( Week of Year(:StartTime,3) ) );


I used your suggestion to create a new column with the Week of Year formula. I added the argument for rule 3 which is, according to the JSL Syntax Reference, ISO Work Week:

From the Syntax Reference:

Week Of Year(date, <rule_n>)

Description

Returns the week of the year that contains a date-time value. Three rules determine when the first w...

With rule 1 (the default), weeks start on Sunday, with the first Sunday of the year being week 2. We...

With rule 2, the first Sunday begins with week 1, with previous days being week 0.

With rule 3, the ISO-8601 week number is returned. Weeks start on Monday. Week 1 is the first week o...