How to convert character date column running APR-14 to JAN-22 into Quarterly (Q1,Q2,Q3,Q4) labels?

[#1]

In the past I have substracted Month only string (JAN) from Jan-14 and then recoded individual months to new label (JAN) = (Q1). However this tack is very manual, is there a formula i can embed to automate this in the future?

[#2]

If you have JMP16 you could first convert the character column to date column with format pattern and then use Quarter function

Names Default To Here(1);

dt = Current Data Table();
Column(dt, 1) << Set Name("MonthYear");
Column(dt, 1) << Data Type(Numeric, 
	Format("Format Pattern", "<Mmm><-><YY>", 35),
	Input Format("Format Pattern", "<Mmm><-><YY>")
);

dt << New Column("Q", Numeric, Ordinal, Formula(Quarter(:MonthYear)));

If you don't have JMP16 you could do "conversion list" of months and then use that instead to get Quarters

Edit:

Formula for JMPs before 16 and no conversion to first column using list of months, contains(), word() and ceiling()

dt << New Column("Q",
	Numeric,
	Ordinal,
	Formula(
		Local({month_list = (month_list = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"})},
			Ceiling(Contains(month_list, Word(1, :MonthYear, "-")) / 3)
		)
	)
);

[#3]

Appreciate the quick response - I'm on JMP 14 so do not have format pattern. I have added a sample file, could you perhaps show me how to use conversion list?

Thanks!

[#4]

Attempted to apply but perhaps i have input formula with basic error?

[#5 · Accepted solution]

Create new column and then add this as formula:

Local({month_list = (month_list = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"})},
			Ceiling(Contains(month_list, Word(1, :MonthYear, "-")) / 3)
		)

Make sure you have correct column name in :MonthYear (in image below it is A)

[#6]

Thanks Jarmo - the local variable feature is new to me but looks like it could be a game changer if i get the hang of it!