Calculate Each quarter 90 days but year end should be 365/366 days for each year

[aaleem]

I have multiple leases, where I have a start date and expiry dates, I would like to get the quarterly dates between the start date and expiry date for each year with the condition that each quarter (Q1, Q2, Q3) should be 90 days but Q4 should be 365 or 366 days from the start date
.
I'm attaching a screenshot for two leases, is there any excel formula or VBA to get this desired result.

thanks
aleem

 

[MARK858]

Can you post your data using the boards XL2BB addin please so that we don't have to retype it to test.
To install XL2BB click the XL2BB icon in the Reply window which is a link to the download and instructions.

 

[aaleem]

Rental Cheque Dates Calculation.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Start Date	02-10-17				Start Date	14-05-19
2		Expiry Date	01-10-22				Expiry Date	13-05-24
3			Quarter
4		Cheques Dates					Cheques Dates			Basis
5			02-10-17		Ist Payment on start Date			14-05-19		Ist Payment on start Date
6			31-12-17	Qtr1	90 Days			12-08-19	Qtr1	90 Days
7			31-03-18	Qtr2	90 Days			10-11-19	Qtr2	90 Days
8			29-06-18	Qtr3	90 Days			08-02-20	Qtr3	90 Days
9			02-10-18	Qtr4	365 days from the start date			14-05-20	Qtr4	366 days from the start date due to leap year
10			31-12-18	Qtr1	90 Days			12-08-20	Qtr1	90 Days
11			31-03-19	Qtr2	90 Days			10-11-20	Qtr2	90 Days
12			29-06-19	Qtr3	90 Days			08-02-21	Qtr3	90 Days
13			02-10-19	Qtr4	365 days from the start date			14-05-21	Qtr4	365 days from the start date
14			31-12-19	Qtr1	90 Days			12-08-21	Qtr1	90 Days
15			30-03-20	Qtr2	90 Days			10-11-21	Qtr2	90 Days
16			28-06-20	Qtr3	90 Days			08-02-22	Qtr3	90 Days
17			02-10-20	Qtr4	366 days from the start date due to leap year			14-05-22	Qtr4	365 days from the start date
18			31-12-20	Qtr1	90 Days			12-08-22	Qtr1	90 Days
19			31-03-21	Qtr2	90 Days			10-11-22	Qtr2	90 Days
20			29-06-21	Qtr3	90 Days			08-02-23	Qtr3	90 Days
21			02-10-21	Qtr4	365 days from the start date			14-05-23	Qtr4	365 days from the start date
22			31-12-21	Qtr1	90 Days			12-08-23	Qtr1	90 Days
23			31-03-22	Qtr2	90 Days			10-11-23	Qtr2	90 Days
24			29-06-22	Qtr3	90 Days			08-02-24	Qtr3	90 Days
25				Qtr4	365 days from the start date				Qtr4	365 days from the start date
Sheet1

Cell Formulas
Range	Formula
H22:H24,H18:H20,H14:H16,H10:H12,H6:H8,C22:C24,C18:C20,C14:C16,C10:C12,C6:C8	C6	=C5+90
C9,H21,H17,H13,C21,C13	C9	=C5+365
C17,H9	C17	=C13+366

 

[aaleem]

Thank you Mark for visiting my post. I tried to get the desired result via VBA, the quarter dates are coming correctly however when it comes to each year-end the dates are not correct. Please see the VBA code. Any suggestions will be much appreciated.

Sub qtrcalc()

Dim StartDate As Date
Dim EndDate As Date
Dim z As Integer
Dim ldate As Date
Dim x As Integer

StartDate = Range("c1")
EndDate = Range("c2")


z = DateDiff("q", StartDate, EndDate) 

ldate = DateAdd("d", 90, StartDate)

            Range("c27").Value = ldate

Range("c27").Value = StartDate

For x = 28 To 28 + z - 2
        
    Cells(x, 3).Value = DateAdd("d", 90, StartDate)
    StartDate = Cells(x, 3)

Next x

End Sub

 

[aaleem]

i think i crack this, still i request the Gurus to see if any better approach can be made, the results are perfect.

Sub qtrcalc()

Dim StartDate As Date
Dim EndDate As Date
Dim z As Integer
Dim ldate As Date
Dim x As Integer
Dim k As Integer
Dim Ydate As Date

StartDate = Range("c1")
EndDate = Range("c2")


z = DateDiff("q", StartDate, EndDate) '+ 1 ' for begining of the month date

ldate = DateAdd("d", 90, StartDate) 'for the end date based on freq

            Range("c27").Value = ldate

Range("c27").Value = StartDate


For x = 28 To 28 + z - 2
    
    Cells(x, 3).Value = DateAdd("d", 90, StartDate)
    
        Ydate = Range("c1").Value
        For k = 31 To 31 + z - 5 Step 4
        Cells(k, 3).Value = DateAdd("yyyy", 1, Ydate)
        Ydate = Cells(k, 3)
        Next k
        
    StartDate = Cells(x, 3)
    
      
Next x


End Sub

 

[pjmorris]

its much easier than using VBA.

Book1
	A
4	02-Oct-17
5	31-Dec-17
6	31-Mar-18
7	29-Jun-18
8	02-Oct-18
9	31-Dec-18
10	31-Mar-19
11	29-Jun-19
12	02-Oct-19
13	31-Dec-19
14	30-Mar-20
15	28-Jun-20
16	02-Oct-20
17	31-Dec-20
18	31-Mar-21
19	29-Jun-21
20	02-Oct-21
Sheet5

Cell Formulas
Range	Formula
A4	A4	=A1
A17:A19,A13:A15,A9:A11,A5:A7	A5	=A4+90
A8	A8	=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
A12,A20,A16	A12	=DATE(YEAR(A8)+1,MONTH(A8),DAY(A8))

 

[pjmorris]

The trick is to use the Date function to calculate the year end. Simply add 1 year to the previous year end and it will take care of the leap years.

HTH

 

[MARK858]

Just a bit of an amendment to the formula by pjmorris to match your rows and to allow for dragging down from C6

Book1
	B	C	D
1	Start Date	02-Oct-17
2	Expiry Date	01-Oct-22
3		Quarter
4	Cheques Dates
5		02-Oct-17
6		31-Dec-17	Qtr1
7		31-Mar-18	Qtr2
8		29-Jun-18	Qtr3
9		02-Oct-18	Qtr4
10		31-Dec-18	Qtr1
11		31-Mar-19	Qtr2
12		29-Jun-19	Qtr3
13		02-Oct-19	Qtr4
14		31-Dec-19	Qtr1
15		30-Mar-20	Qtr2
16		28-Jun-20	Qtr3
17		02-Oct-20	Qtr4
18		31-Dec-20	Qtr1
19		31-Mar-21	Qtr2
20		29-Jun-21	Qtr3
21		02-Oct-21	Qtr4
22		31-Dec-21	Qtr1
23		31-Mar-22	Qtr2
24		29-Jun-22	Qtr3
Sheet3

Cell Formulas
Range	Formula
C5	C5	=C1
C6:C24	C6	=IF(MOD(ROW()-1,4)=0,DATE(YEAR(INDEX(C:C,ROW()-4))+1,10,2),C5+90)

 

[aaleem]

Thank you so much Mark, brilliant piece of the formula.
much appreciate your time.

regards
aleem

 

[MARK858]

You're welcome.
Up to you to decide whether a formula or VBA (BTW I haven't tested your VBA) is easiest for your purposes.

Edit: because of the 2nd half of your image then you will need to reference the Start date in the formula rather than hard code the day/month as I did in my formula (the formula pjmorris posted already does this, my fault for being lazy in mine) i.e.

Book1
	B	C	D	E
1	Start Date	14-May-19
2	Expiry Date	13-May-24
3		Quarter
4	Cheques Dates
5		14-May-19		Ist Payment on start Date
6		12-Aug-19	Qtr1	90 Days
7		10-Nov-19	Qtr2	90 Days
8		08-Feb-20	Qtr3	90 Days
9		14-May-20	Qtr4	365 days from the start date
10		12-Aug-20	Qtr1	90 Days
11		10-Nov-20	Qtr2	90 Days
12		08-Feb-21	Qtr3	90 Days
13		14-May-21	Qtr4	365 days from the start date
14		12-Aug-21	Qtr1	90 Days
15		10-Nov-21	Qtr2	90 Days
16		08-Feb-22	Qtr3	90 Days
17		14-May-22	Qtr4	366 days from the start date due to leap year
18		12-Aug-22	Qtr1	90 Days
19		10-Nov-22	Qtr2	90 Days
20		08-Feb-23	Qtr3	90 Days
21		14-May-23	Qtr4	365 days from the start date
22		12-Aug-23	Qtr1	90 Days
23		10-Nov-23	Qtr2	90 Days
24		08-Feb-24	Qtr3	90 Days
25		14-May-24	Qtr4	Qtr4
Sheet3

Cell Formulas
Range	Formula
C5	C5	=C1
C6:C25	C6	=IF(MOD(ROW()-1,4)=0,DATE(YEAR(INDEX(C:C,ROW()-4))+1,MONTH($C$1),DAY($C$1)),C5+90)