Assign dates as late as possible without duplicates

[dfer4343]

Hello,

I'm looking for a formula that will populate Column C below, using the list from Column E:

Column C must contain dates that:

• Must be from Column E
• Does not repeat (no duplicates)
• Occurs prior to the date from Column B
• Occur as late as possible, given the constraints above (major challenge is to produce as late as possible)

I have tried the RANK function, helper columns, and even differentiating duplicates to no avail.

Any thoughts?

Latest Production Date.xlsx
	A	B	C	D	E
1	Production Run #	Latest Production Date	Actual Production Date ?		Potential Production Dates
2	1	6/10/2025			3/26/2025
3	2	6/17/2025			4/9/2025
4	3	6/23/2025			5/3/2025
5	4	7/5/2025			5/7/2025
6	5	7/5/2025			5/11/2025
7	6	7/8/2025			5/25/2025
8	7	7/8/2025			6/8/2025
9	8	7/14/2025			6/12/2025
10	9	7/17/2025			6/16/2025
11	10	7/25/2025			6/20/2025
12	11	8/14/2025			6/22/2025
13	12	8/22/2025			7/6/2025
14					7/10/2025
15					7/14/2025
16					7/18/2025
17					8/1/2025
18					8/15/2025
19					8/19/2025
Sheet1

 

[AlphaFrog]

You could use the MAXIFS function starting from the last run (#12) and have the formula work up to the previous runs.

Put this in C13 (run #12)

=MAXIFS(E$2:E$19, B13>E$2:E$19)

This returns the last production date previous to the last run.

Put this in C12 and copy it up to C2

=MAXIFS(E$2:E$19,B12>E$2:E$19,C13>E$2:E$19)

This returns the last production date previous to the run and previous to the next run below.


Results:

Production Run #	Latest Production Date	Actual Production Date ?		Potential Production Dates
1​	6/10/2025​	5/25/2025​		3/26/2025​
2​	6/17/2025​	6/8/2025​		4/9/2025​
3​	6/23/2025​	6/12/2025​		5/3/2025​
4​	7/5/2025​	6/16/2025​		5/7/2025​
5​	7/5/2025​	6/20/2025​		5/11/2025​
6​	7/8/2025​	6/22/2025​		5/25/2025​
7​	7/8/2025​	7/6/2025​		6/8/2025​
8​	7/14/2025​	7/10/2025​		6/12/2025​
9​	7/17/2025​	7/14/2025​		6/16/2025​
10​	7/25/2025​	7/18/2025​		6/20/2025​
11​	8/14/2025​	8/1/2025​		6/22/2025​
12​	8/22/2025​	8/19/2025​		7/6/2025​
				7/10/2025​
				7/14/2025​
				7/18/2025​
				8/1/2025​
				8/15/2025​
				8/19/2025​