Period and Number of sub-period closed

[Sunil Pinto]

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

 

[Sunil Pinto]

Thanks, Felixstraube,

Your formula is working fine, if the question is taken from another perspective, it is excellent. Only I struck one of the raw do not know the reason.

			31/12/2022​
	14-Feb-22	13-Feb-23		#SPILL!​

 

[Sunil Pinto]

Thanks, Felixstraube,

Yes, its excellent formula can be used from a difference perspective but one raw formula is not able to give me results. attached details below. Thanks for your efforts.

https://1drv.ms/x/c/4ac6cf5129cd6403/EX1vL7CrSspJl34v_v2ypxwBGu8zmywhcDvU5w0-t6KB5A?e=2qAiO4

 

[hagia_sofia]

Indeed, Felixstraube's formula is excellent. The reason why it is not calculating is because it cannot spill as there is some data in G6 - if you delete it, it should work as intended...

 

[Sunil Pinto]

Indeed, Felixstraube's formula is excellent. The reason why it is not calculating is because it cannot spill as there is some data in G6 - if you delete it, it should work as intended...

super, its clear

 

[Sunil Pinto]

A new requirement has emerged during the project. While Felixstraube has already produced results, I need them modified to meet my specific criteria. I require data for only the last quarter dates in a single column

https://1drv.ms/x/c/4ac6cf5129cd6403/EV9RF289oD5BtAP-Nd_V0WgBtfmqtAOx-rZtzjqbSOOcHw?e=Vyabli

Kindly help me to fix this assignment

 

[felixstraube]

Would this work?:

Book6.xlsx
	A	B	C	D
1
2			Calculation to:	31-Dec-2022
3	Sl.No.	From	To	Last quarter cheque issue date
4	1	15-Jul-2021	14-Jul-2022	15-Apr-2022
5	2	01-Oct-2021	30-Sep-2022	01-Jul-2022
6	3	15-Oct-2021	14-Oct-2022	15-Jul-2022
7	4	15-Oct-2021	14-Oct-2022	15-Jul-2022
8	5	15-Nov-2021	14-Nov-2022	15-Aug-2022
9	6	15-Nov-2021	14-Nov-2022	15-Aug-2022
10	7	01-Jan-2022	31-Dec-2022	01-Oct-2022
11	8	01-Jan-2022	31-Dec-2022	01-Oct-2022
12	9	14-Feb-2022	13-Feb-2023	14-Nov-2022
13	10	21-Feb-2022	20-Feb-2023	21-Nov-2022
14	11	01-Mar-2022	28-Feb-2023	01-Dec-2022
15	12	12-Mar-2022	11-Mar-2023	12-Dec-2022
16	13	15-Mar-2022	14-Mar-2023	15-Dec-2022
17	14	26-Mar-2022	25-Mar-2023	26-Dec-2022
18	15	15-Apr-2022	14-Apr-2023	15-Oct-2022
19	16	05-Sep-2022	04-Sep-2023	05-Dec-2022
20	17	01-Sep-2022	09-Sep-2023	01-Dec-2022
21	18	01-Oct-2022	30-Sep-2023	01-Oct-2022
22	19	10-Oct-2022	09-Oct-2023	10-Oct-2022
23	20	18-Oct-2022	17-Oct-2023	18-Oct-2022
24	21	15-Nov-2022	19-Jan-2023	15-Nov-2022
25	22	26-Nov-2022	25-Nov-2023	26-Nov-2022
26	23	01-Jan-2023	31-Jan-2024
rental (2)

Cell Formulas
Range	Formula
D4:D26	D4	=LET( s, B4, e, MIN(C4, $D$2), d, SEQUENCE(e-s+1,,s), dates, TAKE((FILTER(d, (DAY(d)=DAY(s))*(MOD(MONTH(d), 3)=MOD(MONTH(s), 3)))), -1), IFERROR(dates, "") )

 

[Sunil Pinto]

Would this work?:

Book6.xlsx
	A	B	C	D
1
2			Calculation to:	31-Dec-2022
3	Sl.No.	From	To	Last quarter cheque issue date
4	1	15-Jul-2021	14-Jul-2022	15-Apr-2022
5	2	01-Oct-2021	30-Sep-2022	01-Jul-2022
6	3	15-Oct-2021	14-Oct-2022	15-Jul-2022
7	4	15-Oct-2021	14-Oct-2022	15-Jul-2022
8	5	15-Nov-2021	14-Nov-2022	15-Aug-2022
9	6	15-Nov-2021	14-Nov-2022	15-Aug-2022
10	7	01-Jan-2022	31-Dec-2022	01-Oct-2022
11	8	01-Jan-2022	31-Dec-2022	01-Oct-2022
12	9	14-Feb-2022	13-Feb-2023	14-Nov-2022
13	10	21-Feb-2022	20-Feb-2023	21-Nov-2022
14	11	01-Mar-2022	28-Feb-2023	01-Dec-2022
15	12	12-Mar-2022	11-Mar-2023	12-Dec-2022
16	13	15-Mar-2022	14-Mar-2023	15-Dec-2022
17	14	26-Mar-2022	25-Mar-2023	26-Dec-2022
18	15	15-Apr-2022	14-Apr-2023	15-Oct-2022
19	16	05-Sep-2022	04-Sep-2023	05-Dec-2022
20	17	01-Sep-2022	09-Sep-2023	01-Dec-2022
21	18	01-Oct-2022	30-Sep-2023	01-Oct-2022
22	19	10-Oct-2022	09-Oct-2023	10-Oct-2022
23	20	18-Oct-2022	17-Oct-2023	18-Oct-2022
24	21	15-Nov-2022	19-Jan-2023	15-Nov-2022
25	22	26-Nov-2022	25-Nov-2023	26-Nov-2022
26	23	01-Jan-2023	31-Jan-2024
rental (2)

Cell Formulas
Range	Formula
D4:D26	D4	=LET( s, B4, e, MIN(C4, $D$2), d, SEQUENCE(e-s+1,,s), dates, TAKE((FILTER(d, (DAY(d)=DAY(s))*(MOD(MONTH(d), 3)=MOD(MONTH(s), 3)))), -1), IFERROR(dates, "") )

Would this work?:

Book6.xlsx
	A	B	C	D
1
2			Calculation to:	31-Dec-2022
3	Sl.No.	From	To	Last quarter cheque issue date
4	1	15-Jul-2021	14-Jul-2022	15-Apr-2022
5	2	01-Oct-2021	30-Sep-2022	01-Jul-2022
6	3	15-Oct-2021	14-Oct-2022	15-Jul-2022
7	4	15-Oct-2021	14-Oct-2022	15-Jul-2022
8	5	15-Nov-2021	14-Nov-2022	15-Aug-2022
9	6	15-Nov-2021	14-Nov-2022	15-Aug-2022
10	7	01-Jan-2022	31-Dec-2022	01-Oct-2022
11	8	01-Jan-2022	31-Dec-2022	01-Oct-2022
12	9	14-Feb-2022	13-Feb-2023	14-Nov-2022
13	10	21-Feb-2022	20-Feb-2023	21-Nov-2022
14	11	01-Mar-2022	28-Feb-2023	01-Dec-2022
15	12	12-Mar-2022	11-Mar-2023	12-Dec-2022
16	13	15-Mar-2022	14-Mar-2023	15-Dec-2022
17	14	26-Mar-2022	25-Mar-2023	26-Dec-2022
18	15	15-Apr-2022	14-Apr-2023	15-Oct-2022
19	16	05-Sep-2022	04-Sep-2023	05-Dec-2022
20	17	01-Sep-2022	09-Sep-2023	01-Dec-2022
21	18	01-Oct-2022	30-Sep-2023	01-Oct-2022
22	19	10-Oct-2022	09-Oct-2023	10-Oct-2022
23	20	18-Oct-2022	17-Oct-2023	18-Oct-2022
24	21	15-Nov-2022	19-Jan-2023	15-Nov-2022
25	22	26-Nov-2022	25-Nov-2023	26-Nov-2022
26	23	01-Jan-2023	31-Jan-2024
rental (2)

Cell Formulas
Range	Formula
D4:D26	D4	=LET( s, B4, e, MIN(C4, $D$2), d, SEQUENCE(e-s+1,,s), dates, TAKE((FILTER(d, (DAY(d)=DAY(s))*(MOD(MONTH(d), 3)=MOD(MONTH(s), 3)))), -1), IFERROR(dates, "") )

Wow, felixstraube, that's an amazing formula! Thank you so much for explaining it. I'm really impressed.

 

[felixstraube]

Thanks for the feedback. Happy to help.