Repeat rows for Annually, Quarterly between Start Date and End Date

pdvsa

Board Regular
Joined
Apr 22, 2010
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Hello experts,

I am not sure if I will get a response here but thought I would ask anyways to check if an expert has a suggestion/solution.
I have a file with due dates based on if the frequency is either annually or quarterly.

If annual, use one formula (cell D4)
If quarterly, use another. (cell D5)

I want to repeat t for the number of "hits" for annually and quarterly between the Start Date and End Date. I need the result to be in 1 column.

I assume a solution would be in VBA code (some kind of loop..Im not a programmer). Kindly see the attached file with sample data. After viewing, I think you will be able to see more clearly what I am trying to do. Sorry for any confusion but its better to look at the file to understand completely. The difficult part to me is repeating only between the start date and end date.

Grateful for your assistance
I cant install the the XL2BB (work computer)
below is the link to the file:

Untitled.jpg
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
329
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Here is something you might be able to use. I can't look at your file...

Key in the start and end date and it give you all of the quarters that are inclusive and all of the one-year periods from the start date (meaning, if you start on 1/1, it will give you all of the 12/31's in your range)

MrExcelPlayground2.xlsm
PQRS
1Start Date1/1/2020QuartersYears
2End Date12/31/20243/31/202012/31/2020
36/30/202012/31/2021
49/30/202012/31/2022
512/31/202012/31/2023
63/31/202112/31/2024
76/30/2021
89/30/2021
912/31/2021
103/31/2022
116/30/2022
129/30/2022
1312/31/2022
143/31/2023
156/30/2023
169/30/2023
1712/31/2023
183/31/2024
196/30/2024
209/30/2024
2112/31/2024
Sheet30
Cell Formulas
RangeFormula
R2:R21R2=FILTER(SEQUENCE(Q2-Q1+1,1,Q1,1),(MONTH(SEQUENCE(Q2-Q1+1,1,Q1,1))=3)*(DAY(SEQUENCE(Q2-Q1+1,1,Q1,1))=31)+(MONTH(SEQUENCE(Q2-Q1+1,1,Q1,1))=6)*(DAY(SEQUENCE(Q2-Q1+1,1,Q1,1))=30)+(MONTH(SEQUENCE(Q2-Q1+1,1,Q1,1))=9)*(DAY(SEQUENCE(Q2-Q1+1,1,Q1,1))=30)+(MONTH(SEQUENCE(Q2-Q1+1,1,Q1,1))=12)*(DAY(SEQUENCE(Q2-Q1+1,1,Q1,1))=31),"")
S2:S6S2=FILTER(SEQUENCE(Q2-Q1+1,1,Q1,1),(MONTH(Q1-1)=MONTH(SEQUENCE(Q2-Q1+1,1,Q1,1)))*(DAY(Q1-1)=DAY(SEQUENCE(Q2-Q1+1,1,Q1,1))),"")
Dynamic array formulas.
 

Forum statistics

Threads
1,144,339
Messages
5,723,800
Members
422,517
Latest member
VisioExcel

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