Formula to create date by looking for highest date based on criteria and adding 1.

[DerekWooley]

Hi, I would like to know if there is a formula that could to create a date by looking for the highest date with the same criteria and then adding 1 day to it to make it greater. For example, in the below table I want the last row in Col E to look at item 932 and see that 11/3/2019 is the highest date, then make the last cell in Col E 11/4/2019. Also, for 2972, I want the last row for that item to look at 2/23/2019, and make it 2/24/2019.

Col A	Col B	Col C	Col D	Col E		Col F
Item No.	CUR STK	Projected Stock	Machine	Start Date		Mach Run Qty
2972	2500	2500	Mach 1	6/29/2018		11,000
2972	2500	13500	Mach 1	2/23/2019		17,000
2972	2500	13500	Mach 2	1/11/2019		7,000
2972	2500	37500	Mach 2			8,000
932	4500	4500	Mach 1	6/5/2018		30,000
932	4500	34500	Mach 1	8/8/2019		50,000
932	4500	84500	Mach 2	11/3/2019		27,000
932	4500	111500	Mach 2			27,000

<tbody>
</tbody>

 

[DerekWooley]

I also wanted to add that I want to be able to drag the formula down since I have like 600 of these dates to fill out but I need to avoid circular references since it looks at the same column. Would I use a helper column of some sort?

 

[Peter_SSs]

Note that my dates are in d/m/y format, but see if this, copied down, is what you are after.

Excel Workbook
	A	B	C	D	E	F	G
1	Item No.	CUR STK	Projected Stock	Machine	Start Date	Mach Run Qty
2	2972	2500	2500	Mach 1	29/06/2018	11,000
3	2972	2500	13500	Mach 1	23/02/2019	17,000
4	2972	2500	13500	Mach 2	11/01/2019	7,000
5	2972	2500	37500	Mach 2		8,000	24/02/2019
6	932	4500	4500	Mach 1	5/06/2018	30,000
7	932	4500	34500	Mach 1	8/08/2019	50,000
8	932	4500	84500	Mach 2	3/11/2019	27,000
9	932	4500	111500	Mach 2		27,000	4/11/2019
Max date +1

 

[DerekWooley]

Thank you very much for your help.

 

[Peter_SSs]

Thank you very much for your help.

You are welcome.