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

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

rrtx_mike — Fri, 15 Apr 2016 00:01:25 GMT

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!

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

txnelson — Fri, 15 Apr 2016 12:28:14 GMT

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);

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

rrtx_mike — Fri, 15 Apr 2016 13:48:36 GMT

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 week of the year begins.

‒	With rule 1 (the default), weeks start on Sunday, with the first Sunday of the year being week 2. Week 1 is a partial week or empty.

‒	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 of the year with four days in that year. It is possible for the first or last three days of the year to belong to the neighboring year’s week number.