topic Re: DateTime format issue in Discussions

DateTime format issue

LiPav — Sat, 10 Jun 2023 23:47:03 GMT

Hi,

I have datetime data always saved in format "day/month/year hour.minute" in a column in csv file.

I want to calculate time interval, but JMP flips month and day, when reading the csv file and gives me 734,23 hours instead of less than 24 hours for attached data set.

I have tried to set Input Format and Output Format, but it didn't help. Any ideas ?

dtTime = Open( "DateTime.jmp" );

dtTime:DateTime << Input Format( "d/m/y h:m" );
 
Round( (Col Max( dtAFMtime_xlxs:DateTime ) - Col Min( dtAFMtime_xlxs:DateTime )) / 60 / 60, 2 );

/*Open(
	"DateTime.jmp",
	columns( New Column( "DateTime", Numeric, "Continuous", Format( "d/m/y h.m", 19 ), Input Format( "d/m/y h.m" ) ) ) 
);*/

Regards,

Re: DateTime format issue

txnelson — Wed, 13 Apr 2022 08:27:02 GMT

try this

Names Default To Here( 1 );
//dtTime = Open( "DateTime.jmp" );
dttime = Current Data Table();
dtTime << New Column( "new DateTime",
	set each value(
		Date MDY( Day( dtTime:DateTime ), Month( dtTime:DateTime ), Year( dtTime:DateTime ) )
		+3600 * hour( dtTime:DateTime ) + 60 * Minute( dtTime:DateTime )
		+ Second( dtTime:DateTime )
	),
	Format( "m/d/y h:m" )
);

Re: DateTime format issue

LiPav — Wed, 13 Apr 2022 15:07:28 GMT

Thank you! It makes the trick :)