# Formula for a single list of Dates from two payment cycles and a list of fix dates

#### narendra

##### Board Regular
Can we build a single formula to auto-populate the list of dates as shown in column 'G', based on the payment cycles set in (i) & (ii) and the list of dates provided in (iii), without repeating common dates between them?

For now, I prepared separate list of dates using formulas to calculate each payment cycle from (i) and (ii), and then manually combined them along with list of dates from table (iii) in a chronological order (removing duplicate dates). The desired result is put together in column 'G' for illustration.

Single List of Dates.xlsx
ABCDEFGH
1(i) Loan 'Installment' dates :Merged List of Dates 'populated'based on thetwo Payment Cycles and the list of fixed payment dates
2Enter Loan Date : 29-Feb-16
3Select Loan Tenure : 9Qtr← = Tenure of : 2 Yr 3 MnthsRemarksDate
4Select Installment Frequency : QtrlyLoan Disbursed29-Feb-16
53Add'l payment Date - Fixed4-Apr-16← Non-cyclic payment as per Table in (iii)
6Installment Due29-May-16
7Installment Due29-Aug-16
8(ii) Dates for 'Additional Payments'Add'l payment Date - Fixed30-Aug-16← Non-cyclic payment as per Table in (iii)
9 to be madeAdd'l payment Date - by Freq31-Aug-16← 1st Additional Payment as per freqency from (ii)
10 WITHIN the Loan TenureAdd'l payment Date - by Freq30-Sep-16
11 as per below frequency :Add'l payment Date - by Freq31-Oct-16
12Installment Due29-Nov-16
13Day (date) of Payments : MonthEndSelect betw.: 1 to 30 or MonthEndAdd'l payment Date - by Freq30-Nov-16
14Begin Payment From : AugSelect the MonthAdd'l payment Date - by Freq31-Dec-16
152016Enter Year (data validation is applied)Add'l payment Date - by Freq31-Jan-17
16Payment Frequency : MonthlySelect: Monthly, Qtrly, Half-Yrly, YearlyInstallment Due28-Feb-17← this is also an 'Additional payment' date as per Freq set in (ii)
171Add'l payment Date - by Freq31-Mar-17
18Add'l payment Date - by Freq30-Apr-17
19Installment Due29-May-17
21 made on fixed DatesAdd'l payment Date - Fixed12-Jun-17← Non-cyclic payment as per Table in (iii)
22Source of Funds for makingAdd'l payment Date - by Freq30-Jun-17
24Commission received4-Apr-16Add'l payment Date - Fixed2-Aug-17← Non-cyclic payment as per Table in (iii)
25Sale of car30-Aug-16Installment Due29-Aug-17← Also a date for Non-cyclic payment as per table in (iii)
26Sale of investments12-Jun-17Add'l payment Date - by Freq31-Aug-17
27Term-Deposit Maturity02-Aug-17Add'l payment Date - by Freq30-Sep-17
29To be paid from my savings30-Nov-17Installment Due29-Nov-17
30To be paid from my savings31-Jan-18Add'l payment Date - by Freq30-Nov-17← Also a date for Non-cyclic payment as per table in (iii)
31From Commission receivable30-May-18to be ignored —out of date range as per (i)Add'l payment Date - by Freq31-Dec-17
32From Bonus Receivable02-Aug-18to be ignored —out of date range as per (i)Add'l payment Date - by Freq31-Jan-18← Also a date for Non-cyclic payment as per table in (iii)
33Installment Due28-Feb-18← this is also an Additional payment date (per ii & iii)
34Add'l payment Date - by Freq31-Mar-18
35Add'l payment Date - by Freq30-Apr-18
36Installment Due29-May-18
37←should ignore (return blank or error) for 30-May-18 from table iii
38←should ignore (return blank or error) for 02-Aug-18 from table iii
39..←return blank/error here onwards (no more dates to populate)
40..
Sheet1
Cell Formulas
RangeFormula
D3D3="← = Tenure of : "&IF(C3="Months",INT(B3/12)&" Yr "&MOD(B3,12)&" Mnths",IF(C3="Qtr",INT(B3/4)&" Yr "&(MOD(B3,4)*3)&" Mnths",IF(C3="Half-Yrs",INT(B3/2)&" Yr "&(MOD(B3,2)*6)&" Mnths",IF(C3="Years",B3&" "&C3,"-"))))
B5,B17B5=IF(B4="Monthly",1,IF(B4="Qtrly",3,IF(B4="Half-Yrly",6,IF(B4="Yearly",12,"-"))))
G4G4=\$B\$2
G5G5=B24
G6G6=IF( AND(MONTH(G4)=2, DAY(G4)<>DAY(G\$4)), EOMONTH(G4,B\$5-1) + DAY(G\$4), EDATE(G4,B\$5))
G7G7=IF( AND(MONTH(G6)=2, DAY(G6)<>DAY(G\$4)), EOMONTH(G6,B\$5-1) + DAY(G\$4), EDATE(G6,B\$5))
G8G8=B25
G9G9=IF( B13="MonthEnd", EOMONTH(--(B14&"-"&B15),0), IF(B14="Feb", MIN( B13, DAY(EOMONTH(--(B14&"-"&B15),0)) ), --(B13&"-"&B14&"-"&B15)) )
G34:G35,G31:G32,G27:G28,G23,G17:G18,G14:G15,G10:G11G10=IF( \$B\$13="MonthEnd", EOMONTH(G9+1,\$B\$17-1), IF( (MONTH(EDATE(G9,\$B\$17)) - MONTH(G9)) <> \$B\$17, EOMONTH( DATE(YEAR(G9),MONTH(G9)+\$B\$17,1), 0), EDATE(G9,\$B\$17) ) )
G12G12=IF( AND(MONTH(G7)=2, DAY(G7)<>DAY(G\$4)), EOMONTH(G7,B\$5-1) + DAY(G\$4), EDATE(G7,B\$5))
G13,G30,G22,G20G13=IF( \$B\$13="MonthEnd", EOMONTH(G11+1,\$B\$17-1), IF( (MONTH(EDATE(G11,\$B\$17)) - MONTH(G11)) <> \$B\$17, EOMONTH( DATE(YEAR(G11),MONTH(G11)+\$B\$17,1), 0), EDATE(G11,\$B\$17) ) )
G16,G33,G29G16=IF( AND(MONTH(G12)=2, DAY(G12)<>DAY(G\$4)), EOMONTH(G12,B\$5-1) + DAY(G\$4), EDATE(G12,B\$5))
G19,G36G19=IF( AND(MONTH(G16)=2, DAY(G16)<>DAY(G\$4)), EOMONTH(G16,B\$5-1) + DAY(G\$4), EDATE(G16,B\$5))
G21G21=B26
G24G24=B27
G25G25=IF( AND(MONTH(G19)=2, DAY(G19)<>DAY(G\$4)), EOMONTH(G19,B\$5-1) + DAY(G\$4), EDATE(G19,B\$5))
G26G26=IF( \$B\$13="MonthEnd", EOMONTH(G23+1,\$B\$17-1), IF( (MONTH(EDATE(G23,\$B\$17)) - MONTH(G23)) <> \$B\$17, EOMONTH( DATE(YEAR(G23),MONTH(G23)+\$B\$17,1), 0), EDATE(G23,\$B\$17) ) )
Cells with Data Validation
CellAllowCriteria
B4ListMonthly,Qtrly,Half-Yrly,Yearly
B3Whole number>0
C3ListYears,Months,Qtr,Half-Yrs
B13ListMonthEnd,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31
B14ListJan,Feb,Mar,Apr,Jun,Jul,Aug,Sep,Oct,Nov,Dec
B15Custom=AND(MOD(B15,1)=0, IF(B13="MonthEnd",EOMONTH(--(B14&"-"&B15),0),IF(B14="Feb",MIN(B13,DAY(EOMONTH(--(B14&"-"&B15),0))),--(B13&"-"&B14&"-"&B15))) >B2 )
B16ListMonthly,Qtrly,Half-Yrly,Yearly

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

Replies
1
Views
478
Replies
4
Views
208
Replies
14
Views
260
Replies
5
Views
237
Replies
3
Views
355

1,217,385
Messages
6,136,276
Members
450,001
Latest member
KWeekley08

### 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.

### Which adblocker are you using?

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

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