Starting with the column headings, we use a formula similar to your original one, except instead of referring to what is probably a helper row in your original sheet, the SEQUENCE function is used to create an array of 24 sequential numbers, starting with 0. This array is fed to the EDATE function, which will then produce an array representing representing today's date and sequential months. Those dates are fed into the TEXT function to return just the three-letter month abbreviations, which spill across the row.
Excel Formula:
TEXT(EDATE(TODAY(),SEQUENCE(,24,0)),"MMM")
For the Conditional Formatting (CF) function, it is easier to break it down into several parts:
Excel Formula:
=ISNUMBER(MATCH(MONTH(--(D$2&" 1, 2000")),MOD(SEQUENCE(,LET(d,MONTH(--($C3&" 1, 2000"))-MONTH(--($B3&" 1, 2000"))+1,IF(d<=0,12+d,d)),MONTH(--($B3&" 1, 2000")))-1,12)+1,0))
Somehow, the three-letter month abbreviations need to the expressed in terms of their month numbers. This can be done many different ways. I used a method that constructs a date from the month abbreviation by concatenating the month with a day (1) and a year (2000). The concatenation is still a text representation of the date, e.g., "Sep 1, 2000", so to coerce Excel to convert this to a numeric date value, the double unary operator (--) is used...and then the MONTH function will accept that numeric date as an argument. The choice of day number and year are arbitrary, as the MONTH function will return only the month number for whichever date is passed to it. Within the CF formula, you will see these components:
Excel Formula:
MONTH(--($B3&" 1, 2000")) StartMonthNumber of season
Excel Formula:
MONTH(--($C3&" 1, 2000")) EndMonthNumber of season
Excel Formula:
MONTH(--(D$2&" 1, 2000")) ColumnMonthNumber of the 2-year calendar
...each returning a month number of the cell referenced. For example, if $B3 is Sep and $C3 is Jan, we'll get 9 and 1, the first two formulas will return 9 and 1.
Within the CF formula, these components are used for various purposes. In the LET function, we define the sport season durations (d) in terms of the difference between EndMonthNumber and StartMonthNumber (note 1 is added to account for both start and end months, and not just the intervals):
Excel Formula:
d, MONTH(--($C3&" 1, 2000")) - MONTH(--($B3&" 1, 2000"))+1
This works fine provided EndMonthNumber is greater than StartMonthNumber, but what about the situation where StartMonthNumber is greater than EndMonthNumber... e.g., football from Sep (9) to Jan (1)? For this reason, the LET function includes instructions on how to make this assessment and what to do with the results:
If StartMonthNumber is greater than EndMonthNumber, d will be negative, suggesting that we need the 12-complement, so the season length will be 12+d (d is negative). And if EndMonthNumber is greater than StartMonthNumber, d is taken as the season length. For example, football has StartMonthNumber=9 and EndMonthNumber=1, so 1-9+1=-7, a value <=0, therefore we compute d=12+(-7)=5...the football season is 5 months long.
I don't know if you are familiar with the LET function, but as shown here, it allows for the definition of one or more named variables (in this case "d" is defined as the function described above), and then that shorthand variable name can be used repeatedly later in the LET function (as shown in the IF function where d is needed three times). The final result returned by the LET function is the result produced by its last argument. To summarize then, SeasonLength in months is given by:
Excel Formula:
LET(d,MONTH(--($C3&" 1, 2000"))-MONTH(--($B3&" 1, 2000"))+1,IF(d<=0,12+d,d))
With these various building blocks, we can write a pseudo-formula for one part of the CF formula:
SEQUENCE(, SeasonLength, StartMonthNumber)
This generates an array consisting of a SeasonLength number of elements that begin at StartMonthNumber and increase by 1. For football, this array would be {9,10,11,12,13}...five elements beginning at 9 and increasing by 1.
Because we have crossed the year-to-year boundary (beyond 12 months), we feed this array into a MOD function that produces patterns of integers 1 through 12 (MOD returns the remainder of a number after division by the 2nd argument...12 in this case). To index the array correctly, 1 is subtracted initially before the mod operation and then 1 is added afterward.
Excel Formula:
MOD(SEQUENCE(, SeasonLength, StartMonthNumber)-1, 12) + 1
For the football season example, the evolution of the results looks like this:
MOD( {9,10,11,12,13} - 1, 12 ) + 1
MOD( {8,9,10,11,12}, 12 ) + 1
{8,9,10,11,0} + 1
{9,10,11,12,1}
The final result is an array of month numbers corresponding to the sport's season, so I'll refer to it as the SeasonMonthArray. Finally, this array is used in a MATCH function, where we attempt to find a match for the month number of each column heading (ColumnMonthNumber described earlier) in the SeasonMonthArray. If a MATCH is found--that is, the column heading represents a month falling within the sport's season--then a number will be returned. And if a match is not found, an error will be produced. So we wrap the entire MATCH function with an ISNUMBER function: if a match is found, ISNUMBER returns TRUE, otherwise FALSE.
Excel Formula:
=ISNUMBER( MATCH( ColumnMonthNumber, SeasonMonthArray, 0) )
