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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
Hi Everyone,

Can't we even find a Solution to the above issue via Macro? :(:(:(
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,627
I'm a bit confused. I assume you have a dropdown in C13 and you choose one of these values: C1, C2, C3, etc. Then in M16 you want a value that looks at the C13 code, and the date in C16, and gives the appropriate result from the list? If so, try:

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

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
I'm a bit confused. I assume you have a dropdown in C13 and you choose one of these values: C1, C2, C3, etc. Then in M16 you want a value that looks at the C13 code, and the date in C16, and gives the appropriate result from the list? If so, try:

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

First and Foremost Thanks a lot for your Great Equation.
Unfortunately the equation works according to a calendar year Only.
That's where I have been facing the First Hurdle.

If you check Closely the Dates run up to 2030. Reason for that is this date scheme is a part of Forecasting. Depending on an Input in a Different Cell the Starting "End Date" (ex : March31,2020 - Cell C16) would keep changing and that Date / the respective Month belonging to that Date is what needs to be captured as the First Month of the Year. The Rest of the Dates ex : C17, C18 all gets adjusted...
That's the reason the expected Outcome of C2 shows as 1's x 6 starting from March - considering March as the starting month of that respective forecasting Year.

The Table shows what I would like to capture. And the equation provided by you only works if the starting Date is a date from January.

So while the Dates would constantly change how an year needs to get captured differs.

Do you think something can be done about it Eric ? :unsure::unsure::unsure:
 

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I'm a bit confused. I assume you have a dropdown in C13 and you choose one of these values: C1, C2, C3, etc. Then in M16 you want a value that looks at the C13 code, and the date in C16, and gives the appropriate result from the list? If so, try:

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

Explaining in other words, your assumptions were correct except for the understanding on how a year of a forecasting works, which necessarily wouldn't be a beginning of a Calendar year.
 

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
I'm a bit confused. I assume you have a dropdown in C13 and you choose one of these values: C1, C2, C3, etc. Then in M16 you want a value that looks at the C13 code, and the date in C16, and gives the appropriate result from the list? If so, try:

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

P.S - The other way how I see we can look for a solution is considering that I make this Table Manually despite the change only would happen in the Date Column as the Date would Change. Then looking at the Dropdown in C13, M16 recognizes which Cell to be looked at (from the Table) and gives the desired outcome.

On that Scenario the Date/Month wouldn't really matter and the only thing the Cell M16 would have to consider is what's in the Dropdown and return the Cell Value from the Table. That way no matter what month it is, the Outcome would be exactly what I am trying to capture.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,627

ADVERTISEMENT

I noticed that some of my results were different than yours. So is C16 always the starting month? Will it always be the last day of the month? It should just be a matter of subtracting the starting month from the month of the other dates. It'll make the formula longer, but it's pretty easy.
 

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
I noticed that some of my results were different than yours. So is C16 always the starting month? Will it always be the last day of the month? It should just be a matter of subtracting the starting month from the month of the other dates. It'll make the formula longer, but it's pretty easy.

Yeah, C16 will always be the starting Month and the range would go up to 10 years from that Date.
Yeah, it will always be the last day of that respective month.
"but it's pretty easy." - :love: Really? Please help me out Eric.
 

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
I noticed that some of my results were different than yours. So is C16 always the starting month? Will it always be the last day of the month? It should just be a matter of subtracting the starting month from the month of the other dates. It'll make the formula longer, but it's pretty easy.

Wouldn't matter the length of the Equation. Sheet would be protected so nothing get's messed up, or deleted.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,627
Try:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,829
Messages
5,598,341
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