Adding x number of days to a Character Date column (dd-Mon-YYYY)

DanAlexander

I have a JSL script that periodically pulls data from an Access database. 

 

The access database contains a date column "Available Date" with a dd-Mon-YYYY (E.g., 12-APR-2018) format. What I am trying to do, is create a new column "Target Date" in JMP that adds x number of days to the date in "Available Date" for each row.

 

For example: In the image below, the Target date is equal to Available date + 4 days. I am trying to automatically calculate the Target date based on the Available date. The number of days is based on the "Time (Months)" column.

 

This is what I've tried so far, which obviously does not work. I think I need to convert the "Available Date" column to a numeric data type, then add the number of days & convert back to Character? 

dt << New Column("Target Date", "Character", "Nominal");

dt << For Each Row(
	IF (:"Time (Months)" <= 3, :"Target Date" = :"Available Date" + 4 ),
	IF (:"Time (Months)" > 3, :"Target Date" = :"Available Date" + 7 )
);

Sample Data Table.jmp

txnelson

Unless there is a real reason to keep the Target Date values as a character column, I suggest you convert it to a JMP Date column.

Names Default To Here( 1 );
dt = Current Data Table();

dt << new column( "Target Date", format( "ddmonyyyy") );

for each row(
	if( :"Time (Months)"n <= 3,
		:Target Date = 
			Informat( :Available Date, "ddmonyyyy" ) + In Days( 4 ),
		:Target Date = 
			Informat( :Available Date, "ddmonyyyy" ) + In Days( 7 )
	)
);

It is very easy to convert the above output to character by the below modification

Names Default To Here( 1 );
dt = Current Data Table();

dt << new column( "Target Date", character );

for each row(
	if( :"Time (Months)"n <= 3,
		:Target Date = format(
			Informat( :Available Date, "ddmonyyyy" ) + In Days( 4 ),
			"ddmonyyyy"
		),
		:Target Date = format(
			Informat( :Available Date, "ddmonyyyy" ) + In Days( 7 ),
			"ddmonyyyy"
		)
	)
);

If the output format needs to be exactly as the input data format is, the following will get that accomplished

Names Default To Here( 1 );
dt = Current Data Table();

dt << new column( "Target Date", character );

monthList = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP",
"OCT", "NOV", "DEC"};
for each row(
	if( :"Time (Months)"n <= 3,
		temp = Informat( :Available Date, "ddmonyyyy" ) + In Days( 4 ),
		temp = Informat( :Available Date, "ddmonyyyy" ) + In Days( 7 )
	);
	:Target Date = Substr( "0", Length( Char( Day( temp ) ) ) ) ||
	Char( Day( temp ) ) || "-" || monthList[Month( temp )] || "-" ||
	Char( Year( temp ) );
);

If you wanted to use a formula rather than the For Each Row, this would be for formula for the above output

monthList = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"};
If( :"Time (Months)"n <= 3,
	temp = Informat( :Available Date, "ddmonyyyy" ) + In Days( 4 ),
	temp = Informat( :Available Date, "ddmonyyyy" ) + In Days( 7 )
);
Substr( "0", Length( Char( Day( temp ) ) ) ) || Char( Day( temp ) ) || "-" || monthList[Month( temp )] || "-" ||
Char( Year( temp ) );