Subscribe Bookmark RSS Feed

Re: Subtracting dates in column of table

IrisK

New Contributor

Joined:

Aug 10, 2017

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 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

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
Highlighted
Craige_Hales

Staff

Joined:

Mar 21, 2013

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