running maximum time open

I am trying to create a calculation of the maximum amount time any order of all orders is open. I would like to plot maximum time open (days) vs the date.
The data I have looks something like below where an empty date closed cell indicates the order is still open. Thanks in advance for the help.
(I just noticed the 3 column format is not maintained when actually posted. The date column is just a running list of dates)

Please Login or Register  to view this content.

Based on the example data, please add your expected results and where it would appear. Show us the end goal you are trying to automate.

The results from the sample data set would look something like the following. The only time something interesting happens is when the oldest order is closed.

Please Login or Register  to view this content.

Aw, EDIT your post #1 so you can see the CODE tags I used to make the formatting work, then use them in your posts, fix post #3. Be a quick learner....

Maybe you should post a workbook with all this in it, including the answers, then explain how you got each one. How did you come to 2 in row 5? How did you come to 7 in rows 10 and 11?

I still can't resolve the answers you're giving using only the dates given above. Are you sure you're not inferring additional information from other columns not shown?

No, you won't get away from array formulas on this one. Why would you need to?

I also can't fathom a solution that doesn't require you to sort the data by the first column. I'll ping some others to take a look.

there are surely simpler ways than
cell D2: =IF(MIN(IF(($B$2:$B$7>C2)+($B$2:$B$7=""),IF($A$2:$A$7<=C2,$A$2:$A$7,""),""))=0,0,C2-MIN(IF(($B$2:$B$7>C2)+($B$2:$B$7=""),IF($A$2:$A$7<=C2,$A$2:$A$7,""),"")))
array-entered with ctrl+shift+enter and then copied down

This gives me the correct answers

=MAX(0,C2-MIN(IF((B$2:B$7>C2)+(B$2:B$7=""),A$2:A$7)))

confirmed with CTRL+SHIFT+ENTER

Also be sure to test with a bunch of items with the same open date, but some closed and some not...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.

Originally Posted by pdmbike

I think this works possibly unless there is nothing open.

Joseph's suggestion handled that (if nothing is open it returns a zero). For mine you can tweak it to do the same - MIN always returns zero if there are no matching criteria but if you use SMALL instead then that will give an error if there is nothing open....and you can use IFERROR to generate the required value in that case, i.e.

=IFERROR(MAX(0,C2-SMALL(IF((B$2:B$7>C2)+(B$2:B$7=""),A$2:A$7),1)),0)

confirmed with CTRL+SHIFT+ENTER

The zero at the end is what you get if there is nothing open