How do I apply Custom Date (and Duration) Formats in JMP?

[#1]

Beginning with JMP 16, JMP offers the ability to format dates and times in an infinite number of ways.

1. Paste a raw date into a new column.
2. Right click and select Column Info, change the Data Type to Numeric, the Modeling Type to Continuous.
3. Under Format Type, select Format Pattern, where you will change the input and output.  JMP's default is to use the same format for input as for output.
4. To change format for both inputs and outputs, start with the inputs.
   1. In the User Input Format area, deselect Use Output Format.
   2. Select the Format Pattern.
   3. In pop-up Edit Format Pattern window, scroll through options to create a string to define the formats and select OK.
   
5. Set the same Format Pattern for the output and select Apply.  You will see changed format in the data table.

Click OK to save/ close the Conlumn Info dialog window.

Resources:

• More about how to customize Date-Time formats using Format Patterns

[Judah]

This is very helpful!

I was looking over the date format options and see two of the month descriptions are:

• <Mmm>: Abbreviated month name
• <MMM>: "In-line" month name. Always three letters.

Tested this out and all 12 months give a three character value for both.  What is the difference between <MMM> and <Mmm>?

[BivariateBird24]

Is there a way to save a custom format? And can you set a custom format as the default format? It'd be easier since I'm always having to go in and retype the format I need every time I I make a column for dates/datetimes

[PatrickGiuliano]

I'm including an additional example here to demonstrate what's possible using this feature to format "duration" data in JMP, and because it partly addresses the questions posted by previous users: 

Suppose you want to take an input data format that comes in as Character/ Nominal (Data Type and Modeling Type), and you want to display it and make calculations on it as duration data in JMP (Numeric/ Continuous).

Select the column by right clicking on it and select Column Info or double click on the column to access the Column dialog for Column 1.

Change the Data Type and Modeling Type from Character and Nominal to Numeric and Continuous using the drop-down menu options, respectively.  

Change the Format from "Best" to Format Pattern:

Then click the Set Format Pattern... Button:

Copy and paste the following into the Edit Format Pattern launch window:  

<Day>d <hh24>h <mm>m <ss>s 

and then Click OK.

JMP will indicate the change has been accepted if the data is retained in the respective rows of the column (otherwise the rows information will "disappear."  You can undo the operation easily with Undo (Control + Z) and try again.

Note that if the format is incorrect, JMP will give you some advanced notice in the Edit Format Pattern launch window.

In case you want it, here is the Script that JMP generates for you automatically in the Log that you could use over and over again to do this conversation on future data tables with this same input format:

// Change column info: Column 1
Data Table( "Untitled 2" ):Column 1 << Data Type(
    Numeric,
    Format( "Format Pattern", "<Day>d <hh24>h <mm>m <ss>s", 20, 0 ),
    Input Format( "Format Pattern", "<Day>d <hh24>h <mm>m <ss>s", 0 )
) << Set Modeling Type( "Continuous" );

[PatrickGiuliano]

And here's another great example converting character date/time information into a date/time format in JMP using simple scripting (or column formulas): https://community.jmp.com/t5/Discussions/Character-to-DateTime-Conversion-Help-Needed/m-p/606713#M80...

[hogi]

In the Format Pattern GUI,  is there a possibility to compare the created format pattern with a target (e.g. input pattern)

- like in the Custom Date formula writer from the Data Table Tools Add-in?

[PatrickGiuliano]

@hogi Not that I'm aware of. I do like the Custom Date Formula Writer -- Please feel free to submit a request to the Wishlist (community.jmp.com/wishlist) consider this as a feature improvement under Format Pattern If you decide to do so, please be as specific as possible about what you would like to see (we would appreciate it very much so we can consider the feature more critically and from a UI/UX perspective).

[tsl]

Is it possible to handle milli-sec ( or fractions of seconds ) with custom formats.

Here's my example string :

"20230406052702787"

Decoded this is  YYYMMDDHHmmSS.mmm

or :

Year = 2023

Month = 04

Day = 06

Hour = 05

Minute = 27

Second = 02

Millisecond = 787

I don't strictly need the millisecond right now so I'm going to truncate the last 3 characters of the string, but would be nice to know if there's a format that includes milliseconds

[hogi]

@PatrickGiuliano 
in Format Pattern, is there a placeholder for "entries to ignore", like for  Monday in
Monday, March 22, 2023 5:06:07 +0100

?

Or some keyword to indicate the position of the local time offset:

+0100?

[PatrickGiuliano]

@hogi, Thanks for your question! I'm pretty sure we don't have anything like that. I would make it a bid for the Wishlist though. 

[hogi]

@PatrickGiuliano , ok. 

Then here is the wish: Date Format Pattern - additional functionality 

For "Monday", my actual problem is that <DayOfWeek> expects "Montag" on my (german) system - same issue with March (my system expects "März") . Any chance to specify the regional setting in the Date Format Pattern?

[PatrickGiuliano]

@hogi Thanks!  Not possible to specify regional settings in the Date Format Pattern and I don't think it fits with the current paradigm.  One thing I might suggest, although not particularly convenient, would be to do your format pattern operation with system preference settings and Syntax in English (so set up your preferred date-time format pattern string in English), and then change the preference back to German. I did some very limited testing and it seemed to work pretty well.  

[hogi]

hi @PatrickGiuliano
nice, thanks

[hogi]

another idea: Input Format Pattern - default values 
if a user doesn't specify the month/year, use the current month/year (and not January/1904 :).

or a specific month/year

[hogi]

We wondered if Jmp checks the column context to decide which formula pattern to choose.
the idea:  if one value is > 12, this position cannot be the month - for all of the rows.

• InFormat (column #2)  works row by row and doesn't check.
• the Character to Numeric conversion of column #1 indeed takes into account the entries of  other rows: row 1 is converted correctly because row 2 gives the hint: