Condition to find oldest date

[Scotto811]

Hey everyone, I'm programming a spread sheet and I'm looking for the oldest date in a column with this command; =SMALL(E10:E4000,1)

It's working very nicely, but I want to only find the oldest date if the comulmn next to it is blank. The column next to it is a closed issue date, so if there is no date the issue is open. I was hoping to use the if command to have excel look at the cell next to it, if it has a date then move down to the next until it finds a blank (therefore open) cell and list that as the oldest date.

Attached a screen shot for reference. in the screen shot it would stop on the red box and provide that date 4/7/2010 because the closed issue box is empty next to it.



Uploaded with ImageShack.us

I'm using Cell 10:4000 because the number of rows changes but never goes over 4000, I also have the top 9 cells frozen so they will never be used for calculations.

Thanks for any help in advance!
Scott-

 

[Marcelo Branco]

Hi,

Maybe this array-formula
=MIN(IF(F10:F4000="",IF(E10:E4000<>"",E10:E4000)))
CTRL+SHIFT+ENTER

HTH

M>

 

[Scotto811]

no luck so far, it doesnt know to move down so it just gives the VALUE error in the cell. Trickey lil guy this formula.....

 

[Marcelo Branco]

Did you confirm the formula with Ctrl+Shift+Enter?

Hold down both Ctrl and Shift and hit Enter

M.

 

[Marcelo Branco]

If everything works ok Excel wraps the formula with curly-braces. Like this:
{=MIN(IF(F10:F4000="",IF(E10:E4000<>"",E10:E4000)))}

Take a look at the formula-bar

M.

 

[Scotto811]

I actually re did it and it was working. I filled in a cell and unfortunately though the issue was the formula. Thanks and sorry for the confusion!

 

[Marcelo Branco]

You are welcome.

Glad that its working

M.

 

[Scotto811]

Any change I could add a condition to count the number of dates that are open over 250 days to this? It would have to follow the same formula of needing a blank cell still.

Thanks in advance... again

 

[Marcelo Branco]

Hi,

What precisely do you mean by "dates that are open over 250 days to this"?

M.

 

[Scotto811]

Sorry, re reading this i realize it doesn't make any sense. the =min command doesn't apply here.....

I'm looking for it to count the number of dates that are older than 250 days old, but only when the cells in 410:f4000 are empty. the current date is inputted by the user inside cell; D1 on the spreadsheet.