How to bring in a date variable formatted as mon-yy from Excel into JMP?

[#1]

Hi,

I am importing data from an application into JMP, and the date formats are originally in a month(abr) - year format, so:

jan-09
feb-09
mar-09

etc

i can transform the date format in excel and then specify in JMP, but is there a way for JMP to handle this directly? it seems like JMP only thinks of dates as type numeric / continuous

[#2 · Accepted solution]

Both excel and JMP generally handle time as numeric/continuous, but they differ in how they define the passage of time and the formatting options given to the user.(1=a day in excel and one second in JMP)

I didn't see a day of the month specified in your example, I'll assume that the 15th of the month will work and that you want to map the text to a numeric date. The text can be copied into a formula and the comments (preceded by //) will disappear.


Informat( //transform text to a numeric format
"15" || //add the 15th of the month
Munger( :Text_Mon_Yr, 1, 3 ) || // Extract the first three characters as the month abbrev
"20" || //add 20 to the year (assumes all years after 1999)
Munger( :Text_Mon_Yr, 5, 2 ), //adds the second half of the year code
"ddMonyyyy" // the jmp format to transform[informat] from text to numeric
)

[#3]

thanks for the response. i haven't delved into JMP scripting at all. i added a day field in excel so it's 1/1/2007 and got that to import, but it's really just a summary value for the month.

is there a way to have JMP recognize that it's a month value and order them by date via the JMP UI?

[#4]

This was proposed as a formula. To implement, make a new column, type=numeric, attribute, formula, copy/paste from the example. Change the field:Text_Mon_Yr to the name of character data you are trying to parse into dates. Be sure and set the display format on the column to something easily readable. This can be used for sorting.

An alternative would be to make a character set that will sort as you wish such as 2009_09, 2009_10.

Realize that both Excel and JMP have internal conventions used to map date/time from the underlying numeric to the displayed values. The solution that you implement may involve changing the format on the Excel end, prior to making the transition.

Good luck