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

DerekWooley

New Member
Joined
May 1, 2018
Messages
34
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>
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

DerekWooley

New Member
Joined
May 1, 2018
Messages
34
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,578
Office Version
365
Platform
Windows
Note that my dates are in d/m/y format, but see if this, copied down, is what you are after.

Excel Workbook
ABCDEFG
1Item No.CUR STKProjected StockMachineStart DateMach Run Qty
2297225002500Mach 129/06/201811,000
32972250013500Mach 123/02/201917,000
42972250013500Mach 211/01/20197,000
52972250037500Mach 28,00024/02/2019
693245004500Mach 15/06/201830,000
7932450034500Mach 18/08/201950,000
8932450084500Mach 23/11/201927,000
99324500111500Mach 227,0004/11/2019
Max date +1
 

Watch MrExcel Video

Forum statistics

Threads
1,089,856
Messages
5,410,804
Members
403,330
Latest member
roxmasters2020

This Week's Hot Topics

Top