nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

In search of Date/Time Pain!!

brady_brady

Hi all,

Please shoot me your examples of the most painful date/time formats you have seen from customers. Sometimes the string formats customers give us are a pain to process into dates/times. I'm working on something that should ameliorate this a bit, but could use some more examples.

Please shoot me JMP tables with a column containing the offending string(s).

Don't worry about any of the formats shown in the link below; I've got those covered:

https://dattatreysindol.files.wordpress.com/2011/02/ssrs_date_time_formatting_demo_1016x5701.png

Thanks,

Brady

Accepted answers

All comments

martindemel

Hi Brady,

here you are:

Regards,

Martin

PS: If we could come arround the default format m/d/y in case there is the first cell not clearly defined tat would be great as well, e.g. a kind of dialog "This format cannot be clearly defined, please choose from below or enter your own (which would change the format to character, in case JMP does not know it).

ExcelDateFormats.jmp

Nate_Riordan

Most of the date/time pain I've seen is folks coming from the excel environment wanting to be able to use that style of interface.

A few examples could be:

MAR-16

Apr

Q1FY15

M5FY15

Here Q = quarter, M = Month, FY = fiscal year.

The respective output would indeed need to make some assumptions but could look like:

01Mar2016

01Apr2016

01Jan2015

01May2015

Indeed many of these can be resolved via regex scripting, although I can't say I've seen many excel users willing to a) script, and b) learn regex. Because the excel format is behaving as a simplified form of regex. Sure you could code up or recode character months or month abbreviations and then input that into the format. But it is quite a few steps when a large portion of excel users are familiar with the simplicity of the excel date interface.

martindemel

anything new on the add-in?

thanks,

martin

MikeD_Anderson

One that bugs me is the different ways of determining week number. Occasionally JMP will be out of sync with outlook or other methods.

Best,

Laura_Higgins

This - I was able to use your fancy Data Table Tools add-in to fix it! This came from a customer.

Screen Shot 2017-02-09 at 5.43.00 PM.png

ugly dates.jmp

Jordan_Hiller

Here's a weird edge case, but it is super annoying.

I have attached a data table that I got by using "Internet Open..." on https://en.wikipedia.org/wiki/List_of_current_United_States_Senators.

Look at the "Assumed Office" column. Those dates are as vanilla as can be -- "January 3, 1987". Yet they come into JMP as plain text. And the standard Informat() trick does not help (at least I couldn't make it work)-- one has to write an annoying parser to convert to a JMP date.

This is a shortcoming of the "Internet Open..." feature -- it does not seem to handle dates gracefully. If you save this table out to Excel and reimport thru the wizard, then the date conversion happens automatically.

Senators from Wikipedia.jmp

martindemel

Hi Jordan,

guess it's to reference this example. However, in case you scripted the solution ... Brady's data table tools covert this scenario quite nicely

Data Tabel Tools.PNG

Jordan_Hiller

Thanks Martin! I had forgotten about Brady's beautiful date tool. I should be using it more.

The problem still stands with the "Internet Open..." feature not handling dates well, but I won't submit to tech support as a bug. I believe something is planned for JMP 14 to import data from modern web pages with dynamic content?