Period and Number of sub-period closed

S

Sunil Pinto

New Member
Joined
Jul 31, 2024
Messages
20
Office Version
  1. 2016
Dear All,

"My rental contract spans from June 15, 2024, to September 14, 2027. Rent is paid quarterly on the 20th of each quarter, the first payment beginning on June 20, 2024. How many quarterly cheques will I be issuing till the end of the contract?

The result is 13 cheques.

I found it by using this function as =DATE(2024,SEQUENCE(13,1,6,3),20)

But in the above formula, 13 is manually quoted and tested in the series.

My requirement here is how I can link the period <15/06/2024 to 14/06/2027> to get the result 13. when I change the period I need the result to be changed accordingly.

Can you please help me to fix this task?

regards,

Sunil Pinto
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
It seems to be 50 : 50 that you have 365. If you have here is an attempt:

Excel Formula: 
=LET(
start,A1,
end,B1,
years,YEAR(start)+SEQUENCE(YEAR(end)-YEAR(start)+1,,0),
dates,DROP(REDUCE("",SEQUENCE(4,,3,3),LAMBDA(a,b,VSTACK(a,DATE(years,b,20)))),1),
SUM((dates>=start)*(dates<=end)))
 
Upvote 0
hagia_sofia said:
It seems to be 50 : 50 that you have 365. If you have here is an attempt:

Excel Formula: 
=LET(
start,A1,
end,B1,
years,YEAR(start)+SEQUENCE(YEAR(end)-YEAR(start)+1,,0),
dates,DROP(REDUCE("",SEQUENCE(4,,3,3),LAMBDA(a,b,VSTACK(a,DATE(years,b,20)))),1),
SUM((dates>=start)*(dates<=end)))
Click to expand...
Thank you very much, its working fine
 
Upvote 0
Another option:

Book6.xlsx
AB
1Start15/06/2024
2End14/09/2027
3
4# of chequees13
rental
Cell Formulas
RangeFormula
B4B4=LET( s, B1, e, B2, d, SEQUENCE(e-s+1,,s), COUNT(FILTER(d, (DAY(d)=20)*(MOD(MONTH(d), 3)=1))) )
 
Upvote 0
Dear Team,

Unfortunately, none of the previously suggested formulas have resolved the issue.

I want to provide the actual data and expected output to clarify the problem. Specifically, I need to correct the calculation for the number of quarterly cheques received on 31/12/2022.

Please let me know how to design the formula in excel below is my data

1722584507882.png
 
Upvote 0
Hello again, here is the adjusted formula:

Excel Formula: 
=LET(
start,B2,
end,DATE(2022,12,31),
years,YEAR(start)+SEQUENCE(YEAR(end)-YEAR(start)+1,,0),
dates,SORT(DROP(REDUCE("",SEQUENCE(4,,MONTH(start),3),LAMBDA(a,b,VSTACK(a,DATE(years,b,DAY(start))))),1)),
SUM((dates>=start)*(dates<=end)))

Please note that the end date (i.e. 31/12/2022) is hard-coded, you might wanna make it a reference. If I understand it correctly, start date is taken from the "From" column (only in row 14 it seems to be taken from the column "To" - a mistake probably?).
 
Upvote 1
hagia_sofia said:
Hello again, here is the adjusted formula:

Excel Formula: 
=LET(
start,B2,
end,DATE(2022,12,31),
years,YEAR(start)+SEQUENCE(YEAR(end)-YEAR(start)+1,,0),
dates,SORT(DROP(REDUCE("",SEQUENCE(4,,MONTH(start),3),LAMBDA(a,b,VSTACK(a,DATE(years,b,DAY(start))))),1)),
SUM((dates>=start)*(dates<=end)))

Please note that the end date (i.e. 31/12/2022) is hard-coded, you might wanna make it a reference. If I understand it correctly, start date is taken from the "From" column (only in row 14 it seems to be taken from the column "To" - a mistake probably?).
Click to expand...
I love you. It's beautiful, perfectly matched. The only last raw of my example was mismatched and I used the 'iferror' formula to sort out the problem. Thanks a lot.
 
Upvote 0
Here is the other option with you latest request:

Wouldn't it be for row 1 for example, just 4 cheques? Because the contract ends 14-Jul-2022, so last check should be 15-Apr-2022?
The same for the other rows.

Book6.xlsx
ABCDEFGHI
1
2Calculation to:31-Dec-2022
3Sl.No.FromToActualCheque issue dates
4115-Jul-202114-Jul-2022415-Jul-202115-Oct-202115-Jan-202215-Apr-2022
5201-Oct-202130-Sep-2022401-Oct-202101-Jan-202201-Apr-202201-Jul-2022
6315-Oct-202114-Oct-2022415-Oct-202115-Jan-202215-Apr-202215-Jul-2022
7415-Oct-202114-Oct-2022415-Oct-202115-Jan-202215-Apr-202215-Jul-2022
8515-Nov-202114-Nov-2022415-Jan-202215-Apr-202215-Jul-202215-Oct-2022
9615-Nov-202114-Nov-2022415-Jan-202215-Apr-202215-Jul-202215-Oct-2022
10701-Jan-202231-Dec-2022401-Jan-202201-Apr-202201-Jul-202201-Oct-2022
11801-Jan-202231-Dec-2022401-Jan-202201-Apr-202201-Jul-202201-Oct-2022
12914-Feb-202213-Feb-2023314-Apr-202214-Jul-202214-Oct-2022
131021-Feb-202220-Feb-2023321-Apr-202221-Jul-202221-Oct-2022
141101-Mar-202228-Feb-2023301-Apr-202201-Jul-202201-Oct-2022
151212-Mar-202211-Mar-2023312-Apr-202212-Jul-202212-Oct-2022
161315-Mar-202214-Mar-2023315-Apr-202215-Jul-202215-Oct-2022
171426-Mar-202225-Mar-2023326-Apr-202226-Jul-202226-Oct-2022
181515-Apr-202214-Apr-2023315-Apr-202215-Jul-202215-Oct-2022
191605-Sep-202204-Sep-2023105-Oct-2022
201701-Sep-202209-Sep-2023101-Oct-2022
211801-Oct-202230-Sep-2023101-Oct-2022
221910-Oct-202209-Oct-2023110-Oct-2022
232018-Oct-202217-Oct-2023118-Oct-2022
242115-Nov-202219-Jan-2023 
252226-Nov-202225-Nov-2023 
262301-Jan-202331-Jan-2024 
rental
Cell Formulas
RangeFormula
D24:D26,D19:E23,D12:G18,D4:H11D4=LET( s, B4, e, MIN(C4, $F$2), d, SEQUENCE(e-s+1,,s), dates, TRANSPOSE(FILTER(d, (DAY(d)=DAY(s))*(MOD(MONTH(d), 3)=1))), IFERROR(HSTACK(COUNT(dates), dates), "") )
Dynamic array formulas.
 
Last edited:
Upvote 0
I got it wrong. The quarters start with the starting date. Not the calendar quarters. Right?

Ok here it is corrected:

Book6.xlsx
ABCDEFGH
1
2Calculation to:31-Dec-2022
3Sl.No.FromToActualCheque issue dates
4115-Jul-202114-Jul-2022415-Jul-202115-Oct-202115-Jan-202215-Apr-2022
5201-Oct-202130-Sep-2022401-Oct-202101-Jan-202201-Apr-202201-Jul-2022
6315-Oct-202114-Oct-2022415-Oct-202115-Jan-202215-Apr-202215-Jul-2022
7415-Oct-202114-Oct-2022415-Oct-202115-Jan-202215-Apr-202215-Jul-2022
8515-Nov-202114-Nov-2022415-Nov-202115-Feb-202215-May-202215-Aug-2022
9615-Nov-202114-Nov-2022415-Nov-202115-Feb-202215-May-202215-Aug-2022
10701-Jan-202231-Dec-2022401-Jan-202201-Apr-202201-Jul-202201-Oct-2022
11801-Jan-202231-Dec-2022401-Jan-202201-Apr-202201-Jul-202201-Oct-2022
12914-Feb-202213-Feb-2023414-Feb-202214-May-202214-Aug-202214-Nov-2022
131021-Feb-202220-Feb-2023421-Feb-202221-May-202221-Aug-202221-Nov-2022
141101-Mar-202228-Feb-2023401-Mar-202201-Jun-202201-Sep-202201-Dec-2022
151212-Mar-202211-Mar-2023412-Mar-202212-Jun-202212-Sep-202212-Dec-2022
161315-Mar-202214-Mar-2023415-Mar-202215-Jun-202215-Sep-202215-Dec-2022
171426-Mar-202225-Mar-2023426-Mar-202226-Jun-202226-Sep-202226-Dec-2022
181515-Apr-202214-Apr-2023315-Apr-202215-Jul-202215-Oct-2022
191605-Sep-202204-Sep-2023205-Sep-202205-Dec-2022
201701-Sep-202209-Sep-2023201-Sep-202201-Dec-2022
211801-Oct-202230-Sep-2023101-Oct-2022
221910-Oct-202209-Oct-2023110-Oct-2022
232018-Oct-202217-Oct-2023118-Oct-2022
242115-Nov-202219-Jan-2023115-Nov-2022
252226-Nov-202225-Nov-2023126-Nov-2022
262301-Jan-202331-Jan-2024 
rental
Cell Formulas
RangeFormula
D26,D21:E25,D19:F20,D18:G18,D4:H17D4=LET( s, B4, e, MIN(C4, $F$2), d, SEQUENCE(e-s+1,,s), dates, TRANSPOSE(FILTER(d, (DAY(d)=DAY(s))*(MOD(MONTH(d), 3)=MOD(MONTH(s), 3)))), IFERROR(HSTACK(COUNT(dates), dates), "") )
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
72
Peter_SSs
Peter_SSs
V
  • Question Question
Calculate Number of periods between 2 Dates
Replies
2
Views
340
Vmanchoppy
V
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
126
ellison
E
S
  • Solved
Latest Data pull-up from the corresponding date
Replies
8
Views
192
RobP
R
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
171
Madam_Xavier
M

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

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
Back
Top