topic Re: Subtracting dates in column of table in Discussions

Subtracting dates in column of table

IrisK — Fri, 11 Aug 2017 01:34:35 GMT

Hi Everyone,

Could please help me write a script to subtract the dates by 1 day in one of my table columns and place them into a new column. This is easily done on excel, but I've been having a hard time trying to do this in JMP.

Notice that in the picture, the column titled "TestEndDate" =06/19/2017 ... I want column titled"Test Start Date" to give me dates like 06/18/2017...

Also know that the dates are not all the same in "TestEndDate"...

Re: Subtracting dates in column of table

txnelson — Fri, 11 Aug 2017 05:49:02 GMT

JMP date values are measured in seconds, therefore to subtract a day from a given date, you need to subtract 60*60*24 seconds. The following script creates a data table with a column that has a formula applied to it that subtracts 1 day from the first date column.

Names Default to Here( 1 );
New Table( "Test",
	Add Rows( 10 ),
	New Column( "date",
		Numeric,
		"Continuous",
		Format( "m/d/y", 12 ),
		Input Format( "mmddyyyy" ),
		Set Values(
			[3585254400, 3585254400, 3585254400, 3585254400, 3585254400, 3585254400,
			3585254400, 3585254400, 3585254400, 3585254400]
		)
	),
	New Column( "test end date",
		Numeric,
		"Continuous",
		Format( "m/d/y", 12 ),
		Input Format( "m/d/y" ),
		Formula( :date - 60 * 60 * 24 ),
		Set Selected
	)
)

I also noticed in your attached picture, that your date value seems to be input into a character column, not as a numeric column, with an applied date format. You will need to input the starting date value as a numeric date value if you are going to be able to do your subtraction.

Re: Subtracting dates in column of table

Craige_Hales — Fri, 11 Aug 2017 12:53:12 GMT

Good catch on the character column! (JMP justifies character data left, numeric data right.)

There are some helper functions for adding minutes, hours, days, weeks, and years. inDays(1) is 60*60*24:

Show(
Format( Today(), "y/m/d" ),
Format( Today() - In Days( 1 ), "y/m/d" ),
Format( Today() - In Weeks( 1 ), "y/m/d" ),
Format( Today() - In Years( 1 ), "y/m/d" )
)

Format(Today(), "y/m/d") = "2017/08/11";
Format(Today() - In Days(1), "y/m/d") = "2017/08/10";
Format(Today() - In Weeks(1), "y/m/d") = "2017/08/04";
Format(Today() - In Years(1), "y/m/d") = "2016/08/11";

The function names refer to the argument units and each function returns a JMP duration in seconds.