Convert number to time

[#1]

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?

[#2 · Accepted solution]

What is the number? What are the units?

[#3]

Mark

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

[#4]

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.

[#5]

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

[#6]

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

[#7]

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.