Getting the oldest date with criteria

[Sara_Chase]

I am trying to get the oldest date within a range (from a different worksheet). Each row in the range have their own status. Only those rows whose status <> "CLOSED" or "REJECTED" should be considered in the range.

How do I go about this? I've tried IF statements (maybe I just haven't gotten the right condition yet ***sigh***), used NAME, etc. Any other ideas?

[starguy]

I am trying to get the oldest date within a range (from a different worksheet). Each row in the range have their own status. Only those rows whose status <> "CLOSED" or "REJECTED" should be considered in the range.

How do I go about this? I've tried IF statements (maybe I just haven't gotten the right condition yet ***sigh***), used NAME, etc. Any other ideas?

you need to provide more detail of your data with cell references.

[kincsem]

You might be able to use the strange DMAX

Example

Criteria
OPEN

Date Criteria
13/10/2006 REJECTED
08/11/2006 CLOSED
07/11/2006 OPEN
10/10/2006 CLOSED
03/11/2006 OPEN
24/10/2006 CLOSED
04/10/2006 OPEN
06/10/2006 OPEN
05/10/2006 OPEN
28/10/2006 OPEN
09/10/2006 CLOSED
19/10/2006 OPEN


7/11/06
=DMAX(A4:B16,"Date",B1:B2)


The dates are in cells A5:A16. "Date" is in A4
The "REJECTED, "CLOSED, "OPEN" are in B5:B16

"Criteria" ia in B1, and ="OPEN" IS IN B2

If yopu have problems you could find the maximum value for each work "OPEN, "CLOSED, "REJECTED"

[Sara_Chase]

Hi. Thanks for the tip but I seem to be having problems ... STILL!!!

This is an example of data I am manipulating.

Project________Status_______Date
------- ------ ----
Project 1_______Open_______7-14-2005
Project 1_______Open_______7-14-2005
Project 1_______Closed______7-14-2005
Project 1_______Open_______8-5-2005
Project 2_______Closed______7-14-2005
Project 2_______Closed______7-14-2005
Project 3_______Rejected____7-14-2005
Project 3_______Open_______7-14-2005
Project 3_______Open_______8-5-2005
Project 4_______Open_______10-25-2005
Project 4_______Open_______10-25-2005
Project 4_______Escalated____01-22-2005

So the report should generate something like this:

Project Oldest Date
------- -----------
Project 1______7-14-2005
Project 2______--
Project 3______7-14-2005
Project 4______01-22-2005

Only Open and Escalated items should be considered in determining the oldest date. The criteria that need to be consider are the status and project (> 20 in number).

I am trying to use the DMIN but I can't seem to add-in the project in the condition since this is changing per line.

[Sara_Chase]

still couldn't figure out how to do this ... please help ...

[kincsem]

A1 B1 C1 D1
A2
A3 Project Status
A4 Project 1 Open
A5 Project 1 Escalated
A6
A7 Project Status
A8 Project 2 Open
A9 Project 2 Escalated
A10
A11 Project Status
A12 Project 3 Open
A13 Project 3 Escalated
A14
A15 Project Status
A16 Project 4 Open
A17 Project 4 Escalated
A18
A19
A20
A21
A22
A23
A24 Project Status Date
A25 Project 1 Open 14/07/2005
A26 Project 1 Open 14/07/2005
A27 Project 1 Closed 14/07/2005
A28 Project 1 Open 08/05/2005
A29 Project 2 Closed 14/07/2005
A30 Project 2 Closed 14/07/2005
A31 Project 3 Rejected 14/07/2005
A32 Project 3 Open 14/07/2005
A33 Project 3 Open 08/05/2005
A34 Project 4 Open 25/10/2005
A35 Project 4 Open 25/10/2005
A36 Project 4 Escalated22/01/2005
A37 Project 1 Open 16/07/2003
A38 Project 1 Escalated14/07/2005
A39 Project 1 Closed 14/07/2005
A40 Project 1 Open 08/05/2005
A41 Project 2 Closed 14/07/2005
A42 Project 2 Escalated19/07/2005
A43 Project 3 Rejected 14/07/2005
A44 Project 3 Open 14/07/2005
A45 Project 3 Closed 08/05/2005
A46 Project 4 Escalated25/10/2005
A47 Project 4 Open 05/01/2001
A48 Project 4 Escalated22/01/2005
A49
A50
A51
A52
A53
A54
A55
A56
A57 Project Oldest Date
A58
A59 Project 1 16/07/03 W
A60 Project 2 19/07/05 X
A61 Project 3 08/05/05 Y
A62 Project 4 05/01/01 Z



W <- this is = DMIN($B$24:$D$48,"Date",$B$3:$C5)
X <- this is = DMIN($B$24:$D$48,"Date",$B$7:$C9)
Y <- this is = DMIN($B$24:$D$48,"Date",$B$11:$C13)
Z <- this is = DMIN($B$24:$D$48,"Date",$B$15:$C17)


Sorry about the European date format.
I added extra "invented" project events to help test the formulas.
I didn't see your post (7 days ago). I hope this helps.

The formulas in cells C59; C60; C61; C62 are listed as text below the cells (marked as W, X, Y, Z)

If you like I can email you the Excel spreadsheet. You can PM me your email. Click my name "kincsem" at the top of this post, and click the "send a private message".