Unusual Date format issue hh:mm:ss - yyyymmdd

[#1]

I need to open a log file from a tool that is in simple Tab separated format. This in itself isn't an issue - JMP is the only program I can find that will open the file as it contains the Temperature and Humidity measured every minute since Feb 2012 - it is 184Mb in size!

My issue is the date format in the file - an example is:

17:43:14 - 20120222

hh:mm:ss - yyyymmdd

I am trying to use this for filtering but, of course, JMP sees it as a Character data type.

Using Left and Right in Formulas I have been able to get to a point where I have the Time and Date separately as Character fields. My Date formula looks like this:

Right( Right( :Time Date, 8 ), 2 ) || "/" || Left( Right( Right( :Time Date, 8 ), 4 ), 2 ) || "/" || Left( Right( :Time Date, 8 ), 4 )

But I am lacking the knowledge to go the last step to get a date and Time in a Continuous Numeric format - e.g. ddMMMyyyy hh:mm:ss

Can anyone suggest the best way to do this please? I am happy to look at Scripting options or Formula based options.

[#2 · Accepted solution]

Here is the formula to create a new JMP date/time column from your data input

Informat( Trim( Word( 2, :Time Date, "-" ) ), "yyyymmdd" ) + Informat(
	Trim( Word( 1, :Time Date, "-" ) ),
	"h:m:s"
)

[#3]

Many thanks for that - I had just come across the Informat function and was working through how to use it when your response came through. This is a huge relief as I was beginning to think it was time to give up!

[#4]

Welcome to the Community! I am also enthused that you were already on the path towards your own solution.

[#5]

If you have JMP 16, there is a new Column Property> Format option called Format Pattern that might be of assistance with this as well. See this description. You can start with a character based column, change it to numeric and then change the format to Format Pattern. 

Also you can change the output format to be different than the input format.

That is another way and I this is scriptable too!