Convert character date and time (Jun 2, 2020 8:41:43 AM) to numeric DD MM YYYY HH:MM:SS

[#1]

Normally manipulate this date format in excel 'Jun 2, 2020 8:41:43 AM' to be more JMP friendly but is there a method of completing this activity within the software?

[#2]

If you are using JMP16 you might have luck with Format Pattern depending on your Locale Settings:

[#3]

Thanks for quick response Jarmo! Unfortunately i'm still using JMP 14 so i don't think i have this function.

[#4]

As I don't have access to JMP14 at the moment and my locale is different (I'm not used to 12 hour clock and I have had my fair share of issues how JMP manages dates and times, especially before JMP16.1), testing for me might be a bit finicky. Depending where you need this functionality, you could create a function / custom function or use just as is. The idea is to get your string into date-time value, which JMP can then convert to what ever format you want.

You will have to be careful with different corner cases which can make a mess of the date time and this might not handle all of them or have mistakes in it:

Names Default To Here(1);

//Converts "Jun 2, 2020 12:41:43 PM" formatted string to JMP date-time
convert_str_to_date_time = function({str}, {Default Local},

	//create month list to get month number based on index
	months = {"Jan", "Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"};
	{month_str, day_str, year_str, time_str, ampm_str} = Words(str, ", ");
	{hour_str, minute_str, second_str} = Words(time_str, ":");

	//Date
	year_num = Num(year_str);
	month_num = Contains(months, month_str);
	day_num = Num(day_str);

	//Time
	hour_num = Num(hour_str) + If(ampm_str == "PM", 12, 0); //will need to handle 24 -> 0...
	If(hour_num >= 24, 
		hour_num = 0;
	);
	minute_num = Num(minute_str);
	second_num = Num(second_str);

	//date-time value
	date_time_val = Date DMY(day_num, month_num, year_num) + In Hours(hour_num) + In Minutes(minute_num) + second_num;

	return(date_time_val)
);
str1 = "Jun 2, 2020 8:41:43 AM";
str2 = "Jun 2, 2020 12:41:43 PM";

date_time = convert_str_to_date_time(str1); // As Date(date_time) = 02Jun2020:08:41:43;
Show(As Date(date_time));
date_time = convert_str_to_date_time(str2); // As Date(date_time) = 02Jun2020:00:41:43;
Show(As Date(date_time));

[#5]

Thanks Jarmo - would it be possible to show me how to deploy such a function in the software?

[#6 · Accepted solution]

One option would be to create formula like in the attached file in Column 2.

And if JMP14 has Custom functions, you could use them: JMP15.0 Help - Create Custom Functions, Transforms, and Formats