BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Highlighted
rrtx_mike
Community Trekker

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

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!

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

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 2
txnelson
Super User

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

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

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

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...