Need help converting a date format from character to numeric in JMP 15

Question

Hi,

My Excel sheet has the date format as: Wednesday, November 18, 2020, 12:45:00 PM. Upon importing into JMP 15, it is read as a character.

* Can you please share a method to convert the date from its character format to a numeric-continuous format (e.g., yyyy-mm-dd hh:mm)? I have attached a JMP file for your reference.
* Is there is a way to apply the correct (numeric) date format during import?

Thanks in advance.

Character date format to numeric.jmp

Georg · Answer

Here comes another possibility using regex, probably the shortest implementation would be version 2 below, using additionally informat() in a formula. Once you read the information from the date string, there are many ways to convert it into a numerical date.

If you have the date numerically in Excel, it would be better, to change format in Excel such that JMP can read.

regex is great, I love it!

Names Default To Here( 1 );

// AA for conversion of month
month_AA = Associative Array(
	{"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"},
	{"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"}
);

date_str = "Wednesday, November 18, 2020, 12:45:00 PM";
date_str = "Friday, November 20, 2020, 8:10:00 AM";
regex_str = "(\w+), (\w+) (\d+), (\d+), (\d+):(\d+):(\d+) (PM|AM)";

// 1st version generating a list and building string thereof 
date_component_lst = Regex Match( date_str, regex_str );
date_component_lst[6] = char(num(date_component_lst[6]) + match(date_component_lst[9], "AM", 0, "PM", 12));
date_str1 = date_component_lst[5] || "-" || month_AA[date_component_lst[3]] || "-" || date_component_lst[4] || "T" || date_component_lst[6] || ":"
 || date_component_lst[7] || ":" || date_component_lst[8];

// 2nd version generating a string, to be used by "Parse Date ()" do directly convert it into a date
date_str2 = Regex( date_str, regex_str, "\3 \2 \4 \5:\6:\7 \8" );

txnelson · Answer

Here is a brute force method for getting what you want.  I have attached the complete data table.

If( Row() == 1,
	monthList = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November",
	"December"}
);
myDate = Date MDY(
	Contains( monthList, Word( 2, :Name( "Current Date Format (character)" ), ", " ) ),
	Num( Word( 3, :Name( "Current Date Format (character)" ), ", " ) ),
	Num( Word( 4, :Name( "Current Date Format (character)" ), ", " ) )
);
If(
	Word( -1, :Name( "Current Date Format (character)" ), ", " ) == "PM" & Num(
		Word( 5, :Name( "Current Date Format (character)" ), ",: " )
	) < 12,
	myDate = myDate + Num( Word( 5, :Name( "Current Date Format (character)" ), ",: " ) ) * 3600 + 3600 * 12,
	myDate = myDate + Num( Word( 5, :Name( "Current Date Format (character)" ), ",: " ) ) * 3600
);
myDate = myDate + Num( Word( 6, :Name( "Current Date Format (character)" ), ",: " ) ) * 60 + Num(
	Word( 7, :Name( "Current Date Format (character)" ), ",: " )
);

Character date format to numeric (3).jmp