Please_H
Board Regular
- Joined
- Apr 16, 2017
- Messages
- 181
- Office Version
- 2019
- Platform
- 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
Thanks a lot again for all the Support Given in here...
Hope all of you have a Great Day ahead.
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.