Complex formula to multi-extract smallest and largest date in various ranges

Hi all,

I have a bit of a complex problem to solve...not even ChatGPT was able to handle :D

Bit of a description (all in an Excel table):

1) Cells in range C12:C111 can have one of the following three values: Stage, Task or Milestone
2) Range M12:M111 contains start dates, but only shows where the respective cell in column C is Task or Milestone
3) Range N12:N111 contains end dates, but only shows where the respective cell in column C is Task or Milestone
4) I want to extract the earliest (min) as well as the latest (max) date from the two date ranges for all Task or Milestone values between the Stages. Example:


C12 = Stage, C13 = Milestone or Task, C14 = Milestone or Task, C15 = Stage, C16 = Milestone or Task, C17 = Milestone or Task, C18 = Milestone or Task, C19 = Milestone or Task, C20 = Stage....
In this case, the expected outputs in M12 would be the earliest date of range M13:M14, the expected output in N12 would be the latest date of range N13:N14, the expected output in M15 would be the earliest date of range M16:M19 and the expected output in N15 would be the latest date of range N16:N19 and so on.

The complexity is that new rows can be added at any point in time (as said, it's in an Excel table) so the formulae need to be so dynamic to identify where all the stages are and calculate the in-between min and max dates for each stage that occurs. Hope that makes sense...

Attached is a screenshot of what I am trying to achieve...

This is what ChatGPT recommended, but it returns 0 (indicated as Sat, Jan 00 1900 in screenshot) for when the respective value in column C is "Stage"...

Min Date:

Please Login or Register  to view this content.

Max Date:

Please Login or Register  to view this content.

Sadly can't share the actual file as contains confidential data I can't easily strip out, sorry.

Any smart folks out there who can help solve this problem? Thanks so much!

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Thanks for letting us know. I could have done it easily if I'd known that helper columns were allowed!

ChatGPT makes a real hash of these things - it's really best avoided.