Comma delimited list of months, subject to a few variables that dictate which months

[diversification]

Hi there, I'm trying to write a function that will give me the outputs you see in the yellow cells. I tossed together a quick helper table with the months listed as you see them. My thought was to use a TEXTJOIN function of some sort to pick out relevant months from the helper table and join them, but if it's easier to do without the helper table, that's fine also.

What I'm trying to get the function to do is:
(1) Look at the Start Month, and find that month in Column 1 of the helper table
(2) Look to see if the Start Month needs to be listed twice (Start Month Twice = Yes or No) and either list the start month once or twice.
(3) Return remaining number of months (How Many Months) in order with a comma & space delimiter.

Please note, I need to avoid VBA / Macros, and instead use functions to complete this.

Thanks in advance!

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1		February
2		6
3		No
4		February, March, April, May, June, July
5
6		August
7		9
8		Yes
9		August, August, September, October, November, December, January, February, March
10
Main

Cell Formulas
Range	Formula
B4,B9	B4	=IF(B3="Yes",B1 & ", ","")&TEXTJOIN(", ",,TEXT(DATE(1900,SEQUENCE(B2-(B3="yes"),,MONTH(DATEVALUE("1/"&B1))),1),"mmmm"))

 

[diversification]

Yep, that works - thank you!

The DATEVALUE function isn't one I've used before, but I really like the way you used it here. I just played around with it and it seems that it works properly if I arrange it with the month before the day as well... ie. DATEVALUE(B1&"/1") returns the same value as DATEVALUE("1/"&B1). That flexibility is very nice, but I imagine one must be careful when dealing with dates in such formats as 1/2/2022 -- this could be Jan 2nd 2022 or Feb 1st 2022, however when I tested it, it appears to treat this example as Jan 2nd 2022.

Also, it seems like Excel in interpreting "TRUE" as having a value of 1 when input into an equation, and "FALSE" acts as a 0. I've never come across that before, but it's great to know, because it'll certainly help me shorten a few functions here and there, but it's a bit confusing too... If I try =TRUE=1 or =FALSE=0, both return FALSE. I guess I'll just have to be very cautious about how I use that.


One quick question to close out: is there a benefit to using "1900" for the year value in the DATE function? When I was trying to write this formula myself, I was just leaving out the year, and things seemed to go ok, but if using 1900 is a best practice, I'll adopt that.


Anyway, thank you again!

 

[Fluff]

It's the minus sign that coerces the True to 1 & False to 0 (any maths operation will do it).

is there a benefit to using "1900" for the year value in the DATE function?

I just used 1900 because it's the first year that Excel accepts, but you could use any year

 

[diversification]

It's the minus sign that coerces the True to 1 & False to 0 (any maths operation will do it).

I just used 1900 because it's the first year that Excel accepts, but you could use any year

That all makes perfect sense. Thank you again for the help!!

 

[Fluff]

You're welcome & thanks for the feedback.