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

[#1]

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!

[#2 · Accepted 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);

[#3]

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