Calculate MIN date in column between 2 rows based on the value in another column

[JonReyno]

Hi All,

I have been racking my brain on this one and I can't seem to work out a way around it, so I thought I would put it to a wider audience.

I have a 'Project Schedule' workbook in Excel 365 where you can have 3 types of groups. Stage, Task and Milestone. Each Stage needs to be able to work out the minimum start date out of all the Tasks and Milestones in that stage with a varying number of tasks in each stage. Normally this would be simple enough, but all the stages are stacked so you can't simply just put a min function for the column. There also isn't a unique ID for each stage (unless it is the only way around the problem) to be able to work it out. I was trying to work out if there was a way to say 'get the min value between this row and the next row that has 'Stage' in column C'

I have attached a screenshot of the file but I'm not able to upload the file as it has sensitive information within it. The cells I'm trying to populate with the formula are in column F on each of the 'Stage' rows and they only look at the rows between the current row and the next 'Stage' row.

Hopefully this makes sense and I can get some suggestions on how to try and solve it.

Thank you in advance
Jon

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H
1
2
3
4
5
6
7
8
9
10
11			Stage			03/01/2020
12						21/01/2020
13						11/01/2020
14						08/01/2020
15						03/01/2020
16						17/01/2020
17			Stage			26/12/2019
18						21/01/2020
19						26/12/2019
20						09/01/2020
21						03/01/2020
22						19/01/2020
23						15/01/2020
24			Stage
25						02/12/2019
26
Sheet7

Cell Formulas
Range	Formula
F11, F17	F11	=MIN(OFFSET(C11,1,3,MATCH("Stage",OFFSET(C11,1,0,100,1),0)-1))

 

[JonReyno]

Thank you AlanY, I think that has solved the issue. I had tried OFFSET & MATCH but it wasn't working correctly so I must have not compiled it correctly.

 

[AlanY]

You’re welcome