week start date

[#1]

Hi, I have a column transaction date and need to transform it to the date of Monday in that week. I figured out the below formula and could reach to the right date I need. However it automatically became a character format and no matter how many times i switch it to nominal format it kept changing back to character. Does anyone know why? Thank you!

[#2]

The Short Date() function returns a character string which formats a date or datetime value into m/d/y format.

If you want a JMP date value just remove the Short Date() and then set the format on the column to m/d/y.

Hint: Use the peel button  to remove the outer function from a selection in the Formula Editor.

More here: Using dates, times, datetimes and durations in JMP 

[#3]

Thank you Jeff! The peel button is a new thing to me. 

I have one following question- after removing the short date function I put the new week date into tabulate, but it somehow show the same date multiple times while usually it will show unique dates, as below screenshot. Not sure why the reason. One possible thing i can think of is that the original date column that i used to create this new week start date, has format like "2018/01/01 12:13:05 AM". Do you know a way to fix the tabulate? Thank you!

[#4]

Hi @joann , it is because the function Short Date is specified to return a character string,

see documentation library as attached.

So you should leave away that function to keep it a date, and use the column format instead to control its look.

[#5]

Thank you Georg! I have one following question about this column, could you check the above? Thank you.

[#6 · Accepted solution]

You could create a formula where you remove hours, minutes and seconds from the date you have:

Names Default To Here(1);
dateToday = Today();
Show(As Date(dateToday));
dateToday_noSeconds = dateToday - TimeOfDay(dateToday);
Show(As Date(dateToday_noSeconds));

Or directly with JMP (right click column name -> New Formula Column -> Date Time -> Date):

[#7]

Thank you so much! It worked using the second method. Bear with me with my very basic question-for the first method, where to plug in the script and which part should i change to my date column name "week start"?

[#8]

You would have to create new script and add it there. My example script was just an quick example on the calculation in script editor. You would also have to change it to use column scripting.

Maybe this "full" example can show a bit better how it can be scripted (this as also additional formulas for Monday calculation). You can open new script from File / New / Script and copy paste this there. You would replace :DateTime in the "OnlyDate" column creation with your column name.

Names Default To Here(1);

dt = New Table("firstofweek",
	Add Rows(100),
	Compress File When Saved(1),
	New Column("DateTime",
		Numeric,
		"Continuous",
		Format("Monddyyyy h:m:s", 22, 0),
		Input Format("Monddyyyy h:m:s", 0),
		Formula(Today() - In Days(90-Row())),
		Set Display Width(111)
	)
);
Column(dt, "DateTime") << Delete Formula;

dt << New Column("OnlyDate", Numeric, "Continuous", Format("Monddyyyy h:m:s", 22, 0),
	<< Formula(:DateTime - Time Of Day(:DateTime))
);

dt << New Column("FirstOfWeek1", Numeric, "Continuous", Format("Monddyyyy h:m:s", 22, 0),
	<< Formula(:DateTime - In Days(Day Of Week(:DateTime) - 2) - Time Of Day(:DateTime))
);

dt << New Column("FirstOfWeek2", Numeric, "Continuous", Format("Monddyyyy h:m:s", 22, 0),
	<< Formula(Date Increment(:DateTime, "Week", 0, "start") + In Days(1))
);

Also JMP Scripting Guide and Scripting Index are excellent sources for scripting.

[#9]

As @jthi points out, your WR_Date column must be a datetime column containing a time of day. You need to subtract that off of it to get it to a date value.

You can do that in your formula:

The attached data table has an example.