Subtracting dates in column of table

[#1]

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

[#2 · Accepted solution]

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.

[#3 · Accepted solution]

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.