Convert enum list of months to numbers

I have an enumlist with months ex. (Jan, Feb, Jun, Aug). I need a formula to convert them to a list of their numeric equivalents (1,2,6,9). Does anyone know of an elegant way to do this?

0 23 1,906
23 REPLIES 23

Hi @Glenn_Thorne Check out SWITCH or SUBSTITUTE

SWITCH([Month],
“January”, 1,
“February”, 2,
etc…

Yes, I get that but how to process all items in the list into another list??? Or can this work?

[MonthNum] = SWITCH([Month],
“January”, 1,
“February”, 2,
etc…

Is MonthNum a list of month numbers?

Yessir it is

No easy elegant fix here, you’ll have to construct some formula to reconstruct the list.

substitute(substitute([MonthNum], 1, “January”), 2, “February”), etc

This is close. If I understand it properly, it will change the items in the current list rather than making a new list though. I never thought this would be so difficult actually lol

Completing this out, and putting {1,2,3,4,5,6,7,8,9,10,11,12} for a sample list (giving one example of every month) you get the following:
2X_f_fda2c4cd7efa52f58e57c427017635c362b9a23b.png
substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute({1,2,3,4,5,6,7,8,9,10,11,12}, 12, “December”), 11, “November”), 10, “October”), 9, “September”), 8, “August”), 7, “July”), 6, “June”), 5, “May”), 4, “April”), 3, “March”), 2, “February”), 1, “January”)

2X_3_3b245ef5696cab3767c0c5509dc1d57304625f8f.png

I just did a test, to see if the substitute would preserve the list nature of things: yes it did.
2X_8_84aa3a6e4afc784fc9778960b2b4d120e8e88169.png

thinking this but seems to have a syntax error…

switch([Corporate Remit Months]
,1, “January”
,2, “February”
,3, “March”
,4, “April”
,5, “May”
,6, “June”
,7, “July”
,8, “August”
,9, “September”
,10, “October”
,11, “November”
,12, “December”
, “Error”)

is that a list or a number? You can’t use a list in a switch like this.

its a list. I want to convert a list of month numbers to equivalent list of month text’s

substitute

Now im lost

Maybe take a look at this help document? Check the “Month number to Name section”? https://help.appsheet.com/en/articles/2357278-index

Thats a great article Mike and will work for concerting one month number to text but I dont think it sheds light on how to concert a list of month numbers to month tests. I am doubting that it can be done.

g

Ah… different use case. Could do on the back end (if the list is in google sheets). Otherwise, the formula guru (@Steve) might have a suggestion.

Do you know the formula in google sheets?

If A1 has the month name in it, you could use something like this in cell B1:

=ARRAYFORMULA(MONTH(A1:A&1))
2X_a_a871b3025d70dd480bda30bd1baa821c7e6042c0.png

nope, cuz A1 would have a list in it like 1,2,5 for example lol

or the other way around:
2X_2_28ac534b8a8424ae61350176596c3f464b7c1320.jpeg

@Glenn_Thorne
You can try with this:

CONCATENATE(
	SPLIT(
		IFS(
			IN(1, [ENUMLIST]),"January,"
		)&
		IFS(
			IN(2, [ENUMLIST]),"February,"
		)&
		IFS(
			IN(3, [ENUMLIST]),"March,"
		)&
		IFS(
			IN(4, [ENUMLIST]),"April,"
		)&
		IFS(
			IN(5, [ENUMLIST]),"May,"
		)&
		IFS(
			IN(6, [ENUMLIST]),"June,"
		)&
		IFS(
			IN(7, [ENUMLIST]),"July,"
		)&
		IFS(
			IN(8, [ENUMLIST]),"August,"
		)&
		IFS(
			IN(9, [ENUMLIST]),"September,"
		)&
		IFS(
			IN(10, [ENUMLIST]),"October,"
		)&
		IFS(
			IN(11, [ENUMLIST]),"November,"
		)&
		IFS(
			IN(12, [ENUMLIST]),"December,"
		),
		","
	)
)

Went with Substtiute but I think this would work as well.

thx much

Bingo! Got this to work! Thanks much!!

Top Labels in this Space