topic Re: text DATE-TIME convertion to date-time format in Discussions

text DATE-TIME convertion to date-time format

tcoates — Tue, 19 Dec 2023 23:43:15 GMT

Using JMP 10.0.0

I have a txt string that looks like this in a TXT field. need it as mm/dd/yyyy hr mm as a date-time number.

03/06/2015 1659

I am several frustrating hours into what is likely simple .. and can't do it in JMP or Excel ... so, will parse and reassemble the txt and move forward, but really, this should not be this hard.

Would appreciate your help. Tried "custom formats" in JMP and got nothing ..

Thanks in advance.

Tom

Re: text DATE-TIME convertion to date-time format

tcoates — Tue, 19 Dec 2023 23:45:31 GMT

Correction ... am using JMP 17.0.0 .. dont know why that changed.

tom

Re: text DATE-TIME convertion to date-time format

txnelson — Wed, 20 Dec 2023 03:59:43 GMT

Here is my way of handling this

Names Default To Here( 1 );

text = "03/06/2015 1659";
// JMP will not recognize the hour and minutes without
// a colon(:) between them, so simply add it in
text = left(text,13) || ":" || right(text,2);

// Now the informat() function can read in the 
// text field and convert it into a JMP Date/Time
informat(text, "m/d/y h:m");

Re: text DATE-TIME convertion to date-time format

jthi — Wed, 20 Dec 2023 07:45:13 GMT

I would most likely also go with some sort of text parsing without using format pattern BUT if you want to use format pattern you can, but I'm not sure if I would trust this

Names Default To Here(1);

str = "03/06/2015 1659";

Informat(str, "Format Pattern", "<MM></><DD></><YYYY> <hh24><mm>");

and I would most likely add the separator to time before using this

Names Default To Here(1);

str = "03/06/2015 1659";

Informat(Insert(str, ":", 14), "Format Pattern", "<MM></><DD></><YYYY> <hh24><::><mm>");

And to add this to data table, create new formula column with Numeric data type, use the Informat part as your formula and apply it. Then you can remove the formula and modify formatting as needed.

Re: text DATE-TIME convertion to date-time format

tcoates — Sat, 23 Dec 2023 19:00:20 GMT

Thanks for the reply .. i was afraid of this .. I think i will just adjust the output the primary data source if i can to include the ":" in the correct place.

tom

Re: text DATE-TIME convertion to date-time format

tcoates — Sat, 23 Dec 2023 19:04:25 GMT

Thanks for the reply .. i was afraid of this .. I think i will just adjust the output the primary data source if i can to include the ":" in the correct place.

thanks to JTHI as well who suggested the same. I had tried some versions of your suggestions .. but probably got the syntax wrong.

tom

Re: text DATE-TIME convertion to date-time format

jthi — Sat, 23 Dec 2023 19:24:05 GMT

This might also work (but for whatever reason it didn't work every time I tried it)

Names Default To Here(1);

str = "03/06/2015 1659";

Informat(str, "Format Pattern", "<MM></><DD></><YYYY> <hh24><mm>");