cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
lwx228
Level VIII

How to convert text format Numbers to yyyymmdd hh:mm format?

Hello, everyone!

How to use JSL or formula to convert the text of column 1 into the time format of column 2.

 

Thanks!

2018-11-30_22-27-38.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

Here is the formula for the conversion

Date DMY(
	Num( Substr( :date, 7, 2 ) ),
	Num( Substr( :date, 5, 2 ) ),
	Num( Substr( :date, 1, 4 ) ) ) + 
	60 * 60 * Num( Word( 2, :date, " :" ) ) + 
	60 * Num( Word( -1, :date, " :" ) )
Jim

View solution in original post

Jeff_Perkinson
Community Manager Community Manager

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

@txnelson's formula will work but there's a slightly simpler formula that will work also.

 

JMPScreenSnapz300.png

This one uses Informat() to convert the date and time values. 

 

 

Informat( Word( 1, :Column 1 ) ) +Informat( Word( 2, :Column 1 ), "h:m" )

 

-Jeff

View solution in original post

10 REPLIES 10
lwx228
Level VIII

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

The column names in the diagram should be
yyyymmdd hh:mm
txnelson
Super User

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

Here is the formula for the conversion

Date DMY(
	Num( Substr( :date, 7, 2 ) ),
	Num( Substr( :date, 5, 2 ) ),
	Num( Substr( :date, 1, 4 ) ) ) + 
	60 * 60 * Num( Word( 2, :date, " :" ) ) + 
	60 * Num( Word( -1, :date, " :" ) )
Jim
Jeff_Perkinson
Community Manager Community Manager

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

@txnelson's formula will work but there's a slightly simpler formula that will work also.

 

JMPScreenSnapz300.png

This one uses Informat() to convert the date and time values. 

 

 

Informat( Word( 1, :Column 1 ) ) +Informat( Word( 2, :Column 1 ), "h:m" )

 

-Jeff
lwx228
Level VIII

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

Thanks!
Jim Let me know the principle, Jeff_Perkinson let me learn JMP function.
lwx228
Level VIII

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

How can JMP get the text of year, month and day from the date format?
Sorry to bother you again,Thank you!

 

2018-12-04_22-10-39.png

 

 

 

txnelson
Super User

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

Your question is vague. Please give an example of what the input values looks like, and what you want the output value to be...….
Jim
Alina199121
Level I

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

I have one data with one varieble that can inculde diferent types of dates.
Exmple:
17 January 2015
Or
12-6/2013
Or
20.Jan1997
Or
March 26 2001
Or
12-23-1991
They will changed in code.
So, I need to have date in YYYY-MM-DD format in my second variable.

First var is Mdfc
Second Var is Fdtc

Can you help to have the solution?
Thank you !
txnelson
Super User

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

Here is a format that will work with the examples you provided.  It will have to be tweaked for other combinations of values and structure.  Attached is a sample data table with the formula applied

If( Row() == 1,
	moList = {"January", "February", "March", "April", "May", "June", "July",
	"August", "September", "October", "November", "December"};
	moAbvList = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep",
	"Oct", "Nov", "Dec"};
);
If(
	Word( 3, :Mdfc, " /-" ) == "",
		theDay = Num( Substr( :Mdfc, 1, 2 ) );
		theMonth = Contains( moAbvList, Substr( :Mdfc, 4, 3 ) );
		theYear = Num( Word( -1, :Mdfc, "abcdefghijklmnopqrstuvwxyz" ) );
		Show( theday, themonth, theyear, Substr( :Mdfc, 4, 3 ) );,
	Is Missing( Num( Word( 2, :Mdfc, " /-" ) ) ) == 1,
		theDay = Num( Word( 1, :Mdfc, " /-" ) );
		theMonth = Contains( moList, Word( 2, :Mdfc, " /-" ) );
		theYear = Num( Word( 3, :Mdfc, " /-" ) );,
	Is Missing( Num( Word( 1, :Mdfc, " /-" ) ) ) == 1,
		theDay = Num( Word( 2, :Mdfc, " /-" ) );
		theMonth = Contains( moList, Word( 1, :Mdfc, " /-" ) );
		theYear = Num( Word( 3, :Mdfc, " /-" ) );,
	theDay = Num( Word( 2, :Mdfc, " /-" ) );
	theMonth = Num( Word( 1, :Mdfc, " /-" ) );
	theYear = Num( Word( 3, :Mdfc, " /-" ) );
);
Date MDY( theMonth, theDay, theYear );
Jim
Jeff_Perkinson
Community Manager Community Manager

Re: How to convert text format Numbers to yyyymmdd hh:mm format?

The Format() function is used to convert a JMP date, time or datetime value to a text string.

 

For example:

 

x=informat("20000214");

show(format(x, "m/d/y"));

show(format(x, "d/m/y"));

show(format(x, "Date Long"));

show(format(x, "Monddyyyy"));

Results in this in the log:

 

Format(x, "m/d/y") = "02/14/2000";
Format(x, "d/m/y") = "14/02/2000";
Format(x, "Date Long") = "Monday, February 14, 2000";
Format(x, "Monddyyyy") = "Feb142000";

 

 

-Jeff