Convert enum list of months to numbers

Glenn_Thorne · 08-20-2019 05:07 PM

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?

Lynn

Hi @Glenn_Thorne Check out SWITCH or SUBSTITUTE

MultiTech

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

Glenn_Thorne

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…

MultiTech

Is MonthNum a list of month numbers?

Glenn_Thorne

Yessir it is

MultiTech

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

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

Glenn_Thorne

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

MultiTech

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:

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”)

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

Glenn_Thorne

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”)

MultiTech

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

Glenn_Thorne

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

MultiTech

substitute

Glenn_Thorne

Now im lost

Mike_A

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

Glenn_Thorne

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

Mike_A

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.

Glenn_Thorne

Do you know the formula in google sheets?

Mike_A

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

=ARRAYFORMULA(MONTH(A1:A&1))

Glenn_Thorne

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

Mike_A

or the other way around:

LeventK

@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,"
		),
		","
	)
)

Glenn_Thorne

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

thx much

Glenn_Thorne

Bingo! Got this to work! Thanks much!!