topic How to convert text format Numbers to yyyymmdd hh:mm format? in Discussions

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

lwx228 — Fri, 30 Nov 2018 14:35:10 GMT

Hello, everyone!

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

Thanks!

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

lwx228 — Fri, 30 Nov 2018 14:37:04 GMT

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

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

txnelson — Fri, 30 Nov 2018 15:25:09 GMT

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

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

Jeff_Perkinson — Fri, 30 Nov 2018 22:09:41 GMT

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

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

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

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

lwx228 — Sat, 01 Dec 2018 01:25:28 GMT

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

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

lwx228 — Mon, 03 Dec 2018 14:11:33 GMT

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

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

txnelson — Mon, 03 Dec 2018 15:06:38 GMT

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

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

Jeff_Perkinson — Mon, 03 Dec 2018 18:22:52 GMT

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

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

lwx228 — Mon, 03 Dec 2018 23:55:36 GMT

That's the style,Thanks!

The effect is the same:
format(x, "y/m/d") ———— format(x, "yyyyymmdd")

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

Alina199121 — Wed, 14 Apr 2021 18:23:36 GMT

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 !

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

txnelson — Wed, 14 Apr 2021 22:04:05 GMT

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