Capture 09 Outputs by a Formula or UDF

[Please_H]

Good Morning from Sri Lanka to All of You,

I hope all of you are safe and doing well...

I am in need of finding a Solution to the below 09 Outcomes via a Single Formula or a UDF.

Things to Note :
01. Dates given in C16:C135 constantly Change depending on an input of a Starting Date. In other words - Dates are not static, but Dynamic.
02. All 9 Outcomes I expect the Formula or UDF to capture are Shown as C1, C2, C3... etc in Yellow Square Area
03. D3 to D11 The Outcomes are explained
04. Dash in Yellow area are not Dashes but Zero's, the Cells are on Accounting Format.

Link to Sheet if in need:

Capturing Dates

	Drop-Down Selections
	C1	Year vs Year	Every Month gets Marked as 1
	C2	Half 1	Every 1st 6 months Gets Marked as 1
	C3	Half 2	Every 2nd 6 months Gets Marked as 1
	C4	Q 1	1st Quarter of an Year Gets Marked as 1
	C5	Q 2	2nd Quarter of an Year Gets Marked as 1
	C6	Q 3	3rd Quarter of an Year Gets Marked as 1
	C7	Q 4	4th Quarter of an Year Gets Marked as 1
	C8	Month vs Month	Respective Month which gets Selected Gets Marked as 1
	C9	By Year	From the Starting Month it Gets Marked as 1
			<< Assume the C13 is the Dropdown Selection Area
		Month Name	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	<< M16:M135 is the Area I need a Formula
		March 31, 2020	1	1	-	1	-	-	-	1	1
		April 30, 2020	1	1	-	1	-	-	-	-	2
		May 31, 2020	1	1	-	1	-	-	-	-	3
		June 30, 2020	1	1	-	-	1	-	-	-	4
		July 31, 2020	1	1	-	-	1	-	-	-	5
		August 31, 2020	1	1	-	-	1	-	-	-	6
		September 30, 2020	1	-	1	-	-	1	-	-	7
		October 31, 2020	1	-	1	-	-	1	-	-	8
		November 30, 2020	1	-	1	-	-	1	-	-	9
		December 31, 2020	1	-	1	-	-	-	1	-	10
		January 31, 2021	1	-	1	-	-	-	1	-	11
		February 28, 2021	1	-	1	-	-	-	1	-	12
		March 31, 2021	1	1	-	1	-	-	-	1	13
		April 30, 2021	1	1	-	1	-	-	-	-	14
		May 31, 2021	1	1	-	1	-	-	-	-	15
		June 30, 2021	1	1	-	-	1	-	-	-	16
		July 31, 2021	1	1	-	-	1	-	-	-	17
		August 31, 2021	1	1	-	-	1	-	-	-	18
		September 30, 2021	1	-	1	-	-	1	-	-	19
		October 31, 2021	1	-	1	-	-	1	-	-	20
		November 30, 2021	1	-	1	-	-	1	-	-	21
		December 31, 2021	1	-	1	-	-	-	1	-	22
		January 31, 2022	1	-	1	-	-	-	1	-	23
		February 28, 2022	1	-	1	-	-	-	1	-	24
		March 31, 2022	1	1	-	1	-	-	-	1	25
		April 30, 2022	1	1	-	1	-	-	-	-	26
		May 31, 2022	1	1	-	1	-	-	-	-	27
		June 30, 2022	1	1	-	-	1	-	-	-	28
		July 31, 2022	1	1	-	-	1	-	-	-	29
		August 31, 2022	1	1	-	-	1	-	-	-	30
		September 30, 2022	1	-	1	-	-	1	-	-	31
		October 31, 2022	1	-	1	-	-	1	-	-	32
		November 30, 2022	1	-	1	-	-	1	-	-	33
		December 31, 2022	1	-	1	-	-	-	1	-	34
		January 31, 2023	1	-	1	-	-	-	1	-	35
		February 28, 2023	1	-	1	-	-	-	1	-	36
		March 31, 2023	1	1	-	1	-	-	-	1	37
		April 30, 2023	1	1	-	1	-	-	-	-	38
		May 31, 2023	1	1	-	1	-	-	-	-	39
		June 30, 2023	1	1	-	-	1	-	-	-	40
		July 31, 2023	1	1	-	-	1	-	-	-	41
		August 31, 2023	1	1	-	-	1	-	-	-	42
		September 30, 2023	1	-	1	-	-	1	-	-	43
		October 31, 2023	1	-	1	-	-	1	-	-	44
		November 30, 2023	1	-	1	-	-	1	-	-	45
		December 31, 2023	1	-	1	-	-	-	1	-	46
		January 31, 2024	1	-	1	-	-	-	1	-	47
		February 29, 2024	1	-	1	-	-	-	1	-	48
		March 31, 2024	1	1	-	1	-	-	-	1	49
		April 30, 2024	1	1	-	1	-	-	-	-	50
		May 31, 2024	1	1	-	1	-	-	-	-	51
		June 30, 2024	1	1	-	-	1	-	-	-	52
		July 31, 2024	1	1	-	-	1	-	-	-	53
		August 31, 2024	1	1	-	-	1	-	-	-	54
		September 30, 2024	1	-	1	-	-	1	-	-	55
		October 31, 2024	1	-	1	-	-	1	-	-	56
		November 30, 2024	1	-	1	-	-	1	-	-	57
		December 31, 2024	1	-	1	-	-	-	1	-	58
		January 31, 2025	1	-	1	-	-	-	1	-	59
		February 28, 2025	1	-	1	-	-	-	1	-	60
		March 31, 2025	1	1	-	1	-	-	-	1	61
		April 30, 2025	1	1	-	1	-	-	-	-	62
		May 31, 2025	1	1	-	1	-	-	-	-	63
		June 30, 2025	1	1	-	-	1	-	-	-	64
		July 31, 2025	1	1	-	-	1	-	-	-	65
		August 31, 2025	1	1	-	-	1	-	-	-	66
		September 30, 2025	1	-	1	-	-	1	-	-	67
		October 31, 2025	1	-	1	-	-	1	-	-	68
		November 30, 2025	1	-	1	-	-	1	-	-	69
		December 31, 2025	1	-	1	-	-	-	1	-	70
		January 31, 2026	1	-	1	-	-	-	1	-	71
		February 28, 2026	1	-	1	-	-	-	1	-	72
		March 31, 2026	1	1	-	1	-	-	-	1	73
		April 30, 2026	1	1	-	1	-	-	-	-	74
		May 31, 2026	1	1	-	1	-	-	-	-	75
		June 30, 2026	1	1	-	-	1	-	-	-	76
		July 31, 2026	1	1	-	-	1	-	-	-	77
		August 31, 2026	1	1	-	-	1	-	-	-	78
		September 30, 2026	1	-	1	-	-	1	-	-	79
		October 31, 2026	1	-	1	-	-	1	-	-	80
		November 30, 2026	1	-	1	-	-	1	-	-	81
		December 31, 2026	1	-	1	-	-	-	1	-	82
		January 31, 2027	1	-	1	-	-	-	1	-	83
		February 28, 2027	1	-	1	-	-	-	1	-	84
		March 31, 2027	1	1	-	1	-	-	-	1	85
		April 30, 2027	1	1	-	1	-	-	-	-	86
		May 31, 2027	1	1	-	1	-	-	-	-	87
		June 30, 2027	1	1	-	-	1	-	-	-	88
		July 31, 2027	1	1	-	-	1	-	-	-	89
		August 31, 2027	1	1	-	-	1	-	-	-	90
		September 30, 2027	1	-	1	-	-	1	-	-	91
		October 31, 2027	1	-	1	-	-	1	-	-	92
		November 30, 2027	1	-	1	-	-	1	-	-	93
		December 31, 2027	1	-	1	-	-	-	1	-	94
		January 31, 2028	1	-	1	-	-	-	1	-	95
		February 29, 2028	1	-	1	-	-	-	1	-	96
		March 31, 2028	1	1	-	1	-	-	-	1	97
		April 30, 2028	1	1	-	1	-	-	-	-	98
		May 31, 2028	1	1	-	1	-	-	-	-	99
		June 30, 2028	1	1	-	-	1	-	-	-	100
		July 31, 2028	1	1	-	-	1	-	-	-	101
		August 31, 2028	1	1	-	-	1	-	-	-	102
		September 30, 2028	1	-	1	-	-	1	-	-	103
		October 31, 2028	1	-	1	-	-	1	-	-	104
		November 30, 2028	1	-	1	-	-	1	-	-	105
		December 31, 2028	1	-	1	-	-	-	1	-	106
		January 31, 2029	1	-	1	-	-	-	1	-	107
		February 28, 2029	1	-	1	-	-	-	1	-	108
		March 31, 2029	1	1	-	1	-	-	-	1	109
		April 30, 2029	1	1	-	1	-	-	-	-	110
		May 31, 2029	1	1	-	1	-	-	-	-	111
		June 30, 2029	1	1	-	-	1	-	-	-	112
		July 31, 2029	1	1	-	-	1	-	-	-	113
		August 31, 2029	1	1	-	-	1	-	-	-	114
		September 30, 2029	1	-	1	-	-	1	-	-	115
		October 31, 2029	1	-	1	-	-	1	-	-	116
		November 30, 2029	1	-	1	-	-	1	-	-	117
		December 31, 2029	1	-	1	-	-	-	1	-	118
		January 31, 2030	1	-	1	-	-	-	1	-	119
		February 28, 2030	1	-	1	-	-	-	1	-	120

Thanks a lot again for all the Support Given in here...
Hope all of you have a Great Day ahead.

 

[Eric W]

Sorry, I put the +1 in the wrong spot, try:

=CHOOSE(RIGHT($C$13)+0,1,--(MOD(MONTH(C16)-MONTH(C$16),12)+1<7),--(MOD(MONTH(C16)-MONTH(C$16),12)+1>6),--(MOD(MONTH(C16)-MONTH(C$16),12)+1<4),--AND(MOD(MONTH(C16)-MONTH(C$16),12)+1>3,MOD(MONTH(C16)-MONTH(C$16),12)+1<7),--AND(MOD(MONTH(C16)-MONTH(C$16),12)+1>6,MOD(MONTH(C16)-MONTH(C$16),12)+1<10),--(MOD(MONTH(C16)-MONTH(C$16),12)+1>9),--(MONTH(C16)=MONTH(C$16)),ROWS(M$16:M16))

 

[Please_H]

Sorry, I put the +1 in the wrong spot, try:

=CHOOSE(RIGHT($C$13)+0,1,--(MOD(MONTH(C16)-MONTH(C$16),12)+1<7),--(MOD(MONTH(C16)-MONTH(C$16),12)+1>6),--(MOD(MONTH(C16)-MONTH(C$16),12)+1<4),--AND(MOD(MONTH(C16)-MONTH(C$16),12)+1>3,MOD(MONTH(C16)-MONTH(C$16),12)+1<7),--AND(MOD(MONTH(C16)-MONTH(C$16),12)+1>6,MOD(MONTH(C16)-MONTH(C$16),12)+1<10),--(MOD(MONTH(C16)-MONTH(C$16),12)+1>9),--(MONTH(C16)=MONTH(C$16)),ROWS(M$16:M16))

Wow, Awesome Chief !!!
Works like a charm...

Could you be kind enough to teach me how the equation works please?

Thank you very much Sir.
And Stay Safe.

 

[Eric W]

Sure! Starting with: RIGHT($C$13)+0

C13 is the cell with the Drop-down selection of C1, C2, ... C9. The RIGHT function takes the rightmost character (there's an optional length parameter, but if you leave it off, it defaults to 1), which gives you the number, but as a character. The +0 converts a character number into an actual number.

Next is the CHOOSE function. The first parameter is a number from 1 to n. The next n characters are different results, which can be numbers or other formulas. So if your drop-down is C1, it takes the 1, which then takes the result from the first result parameter, which is also 1.

Now let's look at C2, or 2, which has the result of --(MOD(MONTH(C17)-MONTH(C$16),12)+1<7) on row 17. C16 has the starting date, and MONTH gets the month, which is 3 (March) in your example. The month of C17 is 4 (April). 4-3 = 1, and then add 1 giving 2. This means that the month in C17 is the second month after C16. Then we check to see if it's less than 7 (in the first half of the year). If so, the formula gives a result of TRUE. Then the --(TRUE) part converts the TRUE into a 1, or a FALSE into a 0. The -- is called a double unary, and converts a non-numeric value into a number, much like the +0 did with the RIGHT function.

The last little bit to look at is what happens if the month in C26 is less than C16. In this case you'd get 1-3 = -2. In order to make this a meaningful number saying that January is 10 months after March, we need to add 12 to it. The MOD function generally gives you the remainder when you divide. For example, MOD(23,5) gives you 3, since 23 = 4*5 with a remainder of 3. But another feature of MOD is that if you start with a negative number, it'll add the divisor until you get above 0. So MOD(2,12) will give you 2, but MOD(-2,12) gives you 10. Then in either case, you add 1 so you get the relative offset within the fiscal year.

All of the other formulas from 3-7 use the same logic. 8 uses an = to check for the same month, and 9 just counts rows.

Hope this helps! ?

 

[Please_H]

Sure! Starting with: RIGHT($C$13)+0

C13 is the cell with the Drop-down selection of C1, C2, ... C9. The RIGHT function takes the rightmost character (there's an optional length parameter, but if you leave it off, it defaults to 1), which gives you the number, but as a character. The +0 converts a character number into an actual number.

Next is the CHOOSE function. The first parameter is a number from 1 to n. The next n characters are different results, which can be numbers or other formulas. So if your drop-down is C1, it takes the 1, which then takes the result from the first result parameter, which is also 1.

Now let's look at C2, or 2, which has the result of --(MOD(MONTH(C17)-MONTH(C$16),12)+1<7) on row 17. C16 has the starting date, and MONTH gets the month, which is 3 (March) in your example. The month of C17 is 4 (April). 4-3 = 1, and then add 1 giving 2. This means that the month in C17 is the second month after C16. Then we check to see if it's less than 7 (in the first half of the year). If so, the formula gives a result of TRUE. Then the --(TRUE) part converts the TRUE into a 1, or a FALSE into a 0. The -- is called a double unary, and converts a non-numeric value into a number, much like the +0 did with the RIGHT function.

The last little bit to look at is what happens if the month in C26 is less than C16. In this case you'd get 1-3 = -2. In order to make this a meaningful number saying that January is 10 months after March, we need to add 12 to it. The MOD function generally gives you the remainder when you divide. For example, MOD(23,5) gives you 3, since 23 = 4*5 with a remainder of 3. But another feature of MOD is that if you start with a negative number, it'll add the divisor until you get above 0. So MOD(2,12) will give you 2, but MOD(-2,12) gives you 10. Then in either case, you add 1 so you get the relative offset within the fiscal year.

All of the other formulas from 3-7 use the same logic. 8 uses an = to check for the same month, and 9 just counts rows.

Hope this helps! ?

Mind = Blown KHABOOOOM !!! ??????
Thank you so much,