Capture 09 Outputs by a Formula or UDF

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
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
C1Year vs YearEvery Month gets Marked as 1
C2Half 1Every 1st 6 months Gets Marked as 1
C3Half 2Every 2nd 6 months Gets Marked as 1
C4Q 11st Quarter of an Year Gets Marked as 1
C5Q 22nd Quarter of an Year Gets Marked as 1
C6Q 33rd Quarter of an Year Gets Marked as 1
C7Q 44th Quarter of an Year Gets Marked as 1
C8Month vs MonthRespective Month which gets Selected Gets Marked as 1
C9By YearFrom the Starting Month it Gets Marked as 1
<< Assume the C13 is the Dropdown Selection Area
Month NameC1C2C3C4C5C6C7C8C9C10<< 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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,627
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))
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,627
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

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
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 !!! :eek::eek::eek:😲😲😲😍😍😍
Thank you so much,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,829
Messages
5,598,347
Members
414,232
Latest member
MIA10_KO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top