Date Timeline - Add non-month end dates to a end of month timeline in sequence

[jgreen83]

I have an XIRR schedule on a monthly timeline (using EOMONTH function) that needs to be dynamic such that if the payment dates (Schedule 1) are not on the end of month (Schedule 2), the actual payment date is inserted in sequence using one constant formula. So the formula would need to either return the month end, or the payment date if it falls in sequence.

Example:

Book2
	B	C	D	E	F
7	Schedule 1		Schedule 2		Schedule 3
8
9	Payment Date		Month End		Adjusted
10
11	10/30/2017		9/30/2017		9/30/2017
12	12/14/2017		10/31/2017		10/30/2017
13	1/12/2018		11/30/2017		10/31/2017
14	12/27/2019		12/31/2017		11/30/2017
15	3/30/2020		1/31/2018		12/14/2017
16	6/20/2020		2/28/2018		12/31/2017
17	6/29/2020		3/31/2018		1/12/2018
18	9/28/2020		4/30/2018		1/31/2018
19	12/30/2020		5/31/2018		2/28/2018
20			6/30/2018		3/31/2018
21			7/31/2018		4/30/2018
22			8/31/2018		5/31/2018
23			9/30/2018		6/30/2018
24			10/31/2018		7/31/2018
25			11/30/2018		8/31/2018
26			12/31/2018		9/30/2018
27			1/31/2019		10/31/2018
28			2/28/2019		11/30/2018
29			3/31/2019		12/31/2018
30			4/30/2019		1/31/2019
31			5/31/2019		2/28/2019
32			6/30/2019		3/31/2019
33			7/31/2019		4/30/2019
34			8/31/2019		5/31/2019
35			9/30/2019		6/30/2019
36			10/31/2019		7/31/2019
37			11/30/2019		8/31/2019
38			12/31/2019		9/30/2019
39			1/31/2020		10/31/2019
40			2/29/2020		11/30/2019
41			3/31/2020		12/27/2019
42			4/30/2020		12/31/2019
43			5/31/2020		1/31/2020
44			6/30/2020		2/29/2020
45			7/31/2020		3/30/2020
46			8/31/2020		3/31/2020
47			9/30/2020		4/30/2020
48			10/31/2020		5/31/2020
49			11/30/2020		6/20/2020
50			12/31/2020		6/29/2020
51					6/30/2020
52					7/31/2020
53					8/31/2020
54					9/28/2020
55					9/30/2020
56					10/31/2020
57					11/30/2020
58					12/30/2020
59					12/31/2020
Sheet2 (2)

Cell Formulas
Range	Formula
F13:F14,F59,F55:F57,F51:F53,F46:F48,F42:F44,F18:F40,F16	F13	=EOMONTH(N13,0)

 

[Tetra201]

Try placing the following formula in cell F11 and drag-copying it down as needed:

=IFERROR(SMALL($B$11:$D$50,ROWS(F$11:F11)),"")

 

[jgreen83]

Thank you Tetra201! This is going to work perfectly. I think with the right modification to the "k" part of the function I can get it to ignore duplicate dates as well. I'm super pumped ? thank you so much.