Find Oldest Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to fiind the oldest date of an entry depending on the status of the
case.

A B C
Canada Open 6/25/07
Mexico Open 6/16/07
Brazil Open 6/1/07
Canada Open 6/19/07
Canada Closed 6/2/07

I need to be able find the oldest open date for Canada which would be 6/19/07
according to this example.
 
=MIN(IF((A1:A5="Canada")*(B1:B5="Open"),C1:C5))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Try this array formula** :

=MIN(IF((A1:A5="Canada")*(B1:B5="Open"),C1:C5))

Format as DATE

Better to use cells to hold the criteria:

E1 = Canada
F1 = Open

=MIN(IF((A1:A5=E1)*(B1:B5=F1),C1:C5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
The formula works except for the fact that unfortunately some of the records
have null values in the date field. How do I ignore those and find the oldest
entered date?
I'm assuming I keep returning 1/0/1900 because of the blank records.
 
I tried each of the suggestions and I'm still returning a 0 value. Could
there be something wrong with the formatting of the date in the referenced
cell?
 
Check the dates are true DATES and not TEXT.

Qaspec said:
I tried each of the suggestions and I'm still returning a 0 value. Could
there be something wrong with the formatting of the date in the referenced
cell?
Click to expand...
 
=MIN(IF((A1:A5="Canada")*(B1:B5="Open")*(C1:C5<>""),C1:C5))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top