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

JonReyno

Board Regular
Joined
Jun 8, 2009
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Project Schedule Example.jpg
    Project Schedule Example.jpg
    191.5 KB · Views: 9

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
try this

Book1
ABCDEFGH
1
2
3
4
5
6
7
8
9
10
11Stage03/01/2020
1221/01/2020
1311/01/2020
1408/01/2020
1503/01/2020
1617/01/2020
17Stage26/12/2019
1821/01/2020
1926/12/2019
2009/01/2020
2103/01/2020
2219/01/2020
2315/01/2020
24Stage
2502/12/2019
26
Sheet7
Cell Formulas
RangeFormula
F11, F17F11=MIN(OFFSET(C11,1,3,MATCH("Stage",OFFSET(C11,1,0,100,1),0)-1))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top