topic Re: Date Format help please in Discussions

Date Format help please

ted_ellefson — Mon, 25 Nov 2019 22:45:44 GMT

Please help me with changing my date time column into a useful JMP column

my formay is yyyymmdd hhmmss

(There is a space in the middle)

example is 20191125 132435 for a Nov 25, 2019 at 1:24 pm 35 secons

I'm not sure how to convert this into a useful JMP format.

I've looked through the multiple date/time related posts, but still don't understand what to do. Thank you in advance!!!

Re: Date Format help please

ron_horne — Mon, 25 Nov 2019 23:37:29 GMT

Hi @ted_ellefson

please try this:

https://community.jmp.com/t5/Discussions/Customized-Date-Formats/td-p/2974

let us know if it worked,

Ron

Re: Date Format help please

txnelson — Mon, 25 Nov 2019 23:38:44 GMT

Attached is a data table with the conversion formula included. Here is the formula being used

Date MDY(
	Num( Substr( :my Date, 5, 2 ) ),
	Num( Substr( :my Date, 7, 2 ) ),
	Num( Substr( :my Date, 1, 4 ) )
) + Num( Substr( :my Date, 10, 2 ) ) * 3600
+Num( Substr( :my Date, 12, 2 ) ) * 60
+Num( Substr( :my Date, 14, 2 ) )

Re: Date Format help please

martindemel — Tue, 26 Nov 2019 14:41:49 GMT

Or you may use the data table tools add-in from brady brady, which allows to convert your date values (as character type in your data table) to a numeric date format while setting the splitpoints, matching the parts to the day/month/year/... and saving this into a new column.

https://community.jmp.com/t5/JMP-Add-Ins/Data-Table-Tools-Add-in/ta-p/28582

There you'll find the instruction document as well, the option you will need is under:

Data Table Tools -> Special Formula Columns -> Custom Date Formula Writer

The formula you get is the same as @txnelson created.

Re: Date Format help please

ted_ellefson — Tue, 26 Nov 2019 15:18:24 GMT

THANK-YOU. The formula works very well.

This Community is awesome