Choose Language Hide Translation Bar
Highlighted
Newbie2Jumpie
Level IV

Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

Hi JMP community,

Today I have a special challenge:

In JMP I have numeric entries (presumably OLE, German locale, in seconds). I know the corresponding correct values in EXCEL, e.g.

JMP                      EXCEL

43717,479872      09.09.2019 11:31:01

43717,479902      09.09.2019 11:31:04

43717,479884      09.09.2019 11:31:02

Could you show me a JSL way how to convert these JMP entries (in seconds) into the desired datetime format like shown in the EXCEL column? I tried several approaches but couldn't figure it out.

Thank you very much!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Newbie2Jumpie
Level IV

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

My solution:

Tx' format approach:

format(in days(43717)-in days(1)-in years(4) + .479872*indays(1),"m/d/y h:m:s");

My computaion approach (assumes OLE timestring is character and
relevant info extracted into utility fields, not typo checked):

new column("My_OLE",numeric,"continuous",formula(format(in
days(:"OLE_days"-in days(1)-in years(:"OLE_years") +
:"OLE_hhmmss"*indays(1),"d/m/y h:m:s"))) ;

View solution in original post

5 REPLIES 5
Highlighted
txnelson
Super User

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

I don't know if this will help at all, but here is a brut force way of doing the conversion.  Note that the initial setting of the JMP date value is in U.S.  numeric structure

Names Default To Here( 1 );
JMPValue = 43717.479872;
// Add the number of seconds different from the JMPValue to the
// JMP date time value for 09.09.2019 11:31:01
JMPDateTime = JMPValue + 3650829743.52013;
// Display it's value using a standard JMP format
Show( Format( JMPDateTime, "d/m/y h:m:s" ) );
// Now show the same JMPDateTime but using a Custom
// format to change the value into exactly the desired
// structure

Show(
	Format(
		JMPDateTime,
		"Custom",
		Formula(
			Trim( Substr( " 0", -1 * Length( Char( Day( value ) ) ), 1) ) || Char( Day( value ) )
			|| "." ||
			Trim( Substr( " 0", -1 * Length( Char( Month( value ) ) ), 1) ) || Char( Month( value ) )
			|| "." ||
			Char( Year( value ) )
			|| " " ||
			Trim( Substr( " 0", -1 * Length( Char( Hour( value ) ) ), 1) ) || Char( Hour( value ) )
			|| ":" ||
			Trim( Substr( " 0", -1 * Length( Char( Minute( value ) ) ), 1) ) || Char( Minute( value ) )
			|| ":" ||
			Trim( Substr( " 0", -1 * Length( Char( Floor( Second( value ) ) ) ), 1) ) || Char( Floor( Second( value ) ) )
		),
		40
	)
);

Which yields the results

Format(JMPDateTime, "d/m/y h:m:s") = "09/09/2019 11:31:01 AM";
Format(JMPDateTime, "Custom", Formula(Trim(Substr(" 0", -1 * Length(Char(Day(value))), 1)) || Char(Day(value)) || "." || Trim(Substr(" 0", -1 * Length(Char(Month(value))), 1)) || Char(Month(value)) || "." || Char(Year(value)) || " " || Trim(Substr(" 0", -1 * Length(Char(Hour(value))), 1)) || Char(Hour(value)) || ":" || Trim(Substr(" 0", -1 * Length(Char(Minute(value))), 1)) || Char(Minute(value)) || ":" || Trim(Substr(" 0", -1 * Length(Char(Floor(Second(value)))), 1)) || Char(Floor(Second(value)))), 40) = "09.09.2019 11:31:01";

 

Jim
Highlighted
pmroz
Super User

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

Could you provide more information about the JMP values in seconds?  If I apply Jim's idea all three values are the same.

jmp_list = {43717.479872, 43717.479902, 43717.479884};
excel_list = {};
for (i = 1, i <= nitems(jmp_list), i++,
	one_dt = jmp_list[i];
// Add the number of seconds different from the JMPValue to the
// JMP date time value for 09.09.2019 11:31:01
	jmp_dt = one_dt + 3650829743.52013;
// Display its value using a standard JMP format
	dtc = Format( jmp_dt, "d/m/y h:m:s" ) ;

	dtc = substitute(dtc, "/", ".");
	excel_list[i] = dtc;
);
show(excel_list);

  

excel_list = {"09.09.2019 11:31:01 AM", "09.09.2019 11:31:01 AM", "09.09.2019 11:31:01 AM"};

The first two entries are 3 seconds apart, but the numbers are .000030 apart

The first and third entries are 1 second apart but the numbers are .000012 apart.

 

What are the units of the "JMP values in seconds" column?

Highlighted
Newbie2Jumpie
Level IV

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

Background is, this is a special OLE based datetime value. As far I was
told it seems to be seconds after 1900. EXCEL imports these values
correctly (sample values on the right). JMP doesn't.



Let's just simplify:

I don't mind whether the eventual JMP display format is US, international,
or European. Most important is a working conversion to a more
readable/common JMP datetime format. The EXCEL column shows what the
correct result should look like.



BTW, the two approaches don't work properly. Both set all entries to
09.09.2019 11:31:01.
Highlighted
txnelson
Super User

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

@Newbie2Jumpie 

You are correct......I took the wrong approach in trying to solve the issue.  The date values you have are actually Excel date/time values.  They are based upon the number of days since 01JAN1900, (43717), for the days, and a proportion of a day, (.479884), for the time and they start with day 1.  JMP base is the number of seconds since 01JAN1904 and starts at day 0.  So the conversion formula is:

in days(43717)-in days(1)-in years(4) + .479872*indays(1)

This can be displayed in the following JSL

format(in days(43717)-in days(1)-in years(4) + .479872*indays(1),"m/d/y h:m:s");

format(in days(43717)-in days(1)-in years(4) + .479902*indays(1),"m/d/y h:m:s");

format(in days(43717)-in days(1)-in years(4) + .479884*indays(1),"m/d/y h:m:s");
format(in days(43717)-in days(1)-in years(4) + .479872*indays(1),"m/d/y h:m:s")
/*:

"09/09/2019 11:31:01 AM"
//:*/
format(in days(43717)-in days(1)-in years(4) + .479902*indays(1),"m/d/y h:m:s");
/*:

"09/09/2019 11:31:04 AM"
//:*/
format(in days(43717)-in days(1)-in years(4) + .479884*indays(1),"m/d/y h:m:s");
/*:

"09/09/2019 11:31:02 AM"

 

Jim
Highlighted
Newbie2Jumpie
Level IV

Re: Convert OLE date (in seconds) to DD.MM.YYYY HH:MM:SS (scripting)

My solution:

Tx' format approach:

format(in days(43717)-in days(1)-in years(4) + .479872*indays(1),"m/d/y h:m:s");

My computaion approach (assumes OLE timestring is character and
relevant info extracted into utility fields, not typo checked):

new column("My_OLE",numeric,"continuous",formula(format(in
days(:"OLE_days"-in days(1)-in years(:"OLE_years") +
:"OLE_hhmmss"*indays(1),"d/m/y h:m:s"))) ;

View solution in original post

Article Labels

    There are no labels assigned to this post.