topic Re: Convert number to time in Discussions

Convert number to time

SpannerHead — Thu, 22 May 2025 15:02:08 GMT

I have a column of data that comes in as numerical values with either 7 or 8 digits and I need that in the HH,MM,SS format but everything I've tried yields failure. Any obvious trick I'm missing?

Re: Convert number to time

Mark_Bailey — Thu, 22 May 2025 15:37:09 GMT

What is the number? What are the units?

Re: Convert number to time

SpannerHead — Thu, 22 May 2025 16:22:20 GMT

Mark

One of the numbers is 61412. I believe this represents 6:14:12AM.

Re: Convert number to time

jthi — Thu, 22 May 2025 17:05:58 GMT

61412 has 5 digits, not seven or eight. Most likely you can convert your numbers? to strings and use informat with format pattern to turn it into a JMP datenum.

Re: Convert number to time

Mark_Bailey — Thu, 22 May 2025 17:52:52 GMT

New Table( "Time",
	Add Rows( 2 ),
	New Column( "Time as Coded Number",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [61412, 142954] )
	),
	New Column( "Time",
		Numeric,
		"Continuous",
		Format( "h:m:s", 12, 0 ),
		Input Format( "h:m:s", 0 ),
		Formula(
			t1 = If( :Time as Coded Number < 100000,
				"0",
				""
			) || Char( :Time as Coded Number );
			In Hours( Num( Left( t1, 2 ) ) )
			+In Minutes( Num( Substr( t1, 3, 2 ) ) ) + Num( Right( t1, 2 ) );
		)
	)
)

You need a formula (or script) to convert the original 5 or 6 digits (assuming hh:mm:ss coding for a 24-hour clock) to the number of seconds (JMP's time base) and change the column format.

Re: Convert number to time

SpannerHead — Thu, 22 May 2025 19:08:03 GMT

I'm not good with numbers. I was mixing up with the date information but that converted readily.

Re: Convert number to time

jthi — Fri, 23 May 2025 04:15:03 GMT

Here is format pattern option, note that if your time is in 12-hour format, it will need some changes

InFormat(Right("0" || Char(:Column 1), 6), "Format Pattern", "<hh><:?><mm><:?><ss>")