cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
IrisK
Level II

Subtracting dates in column of table

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 SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Subtracting dates in column of table

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.

Jim

View solution in original post

Craige_Hales
Super User

Re: Subtracting dates in column of table

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.

Craige

View solution in original post

2 REPLIES 2
txnelson
Super User

Re: Subtracting dates in column of table

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.

Jim
Craige_Hales
Super User

Re: Subtracting dates in column of table

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.

Craige