topic Re: Adding x number of days to a Character Date column (dd-Mon-YYYY) in Discussions

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

DanAlexander — Fri, 09 Jun 2023 22:08:48 GMT

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 )
);

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

dale_lehman — Thu, 18 Mar 2021 15:10:59 GMT

The attached table adds 4 days to your available date column to get the target date. Steps: change the data type for available date to numeric/continuous with appropriate date formatting; then use the Date Increment function to add 4 days to the available date, finally change the formatting for the resulting target date column to match what you used in the available date column.

One additional note - in your example, it looks like you added a week (7 days rather than 4).

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

txnelson — Thu, 18 Mar 2021 16:03:01 GMT

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 ) );

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

DanAlexander — Thu, 18 Mar 2021 19:25:07 GMT

This is excellent, exactly what I was trying to do - and so simple! Thank you for the help. I will leave it in the date format.