How do I find the earliest dates in a range of dates?

[JJ]

In order to do what I ask, I know that I am supposed to use the MIN
function, but there is a twist to what I want to do.

I have a set of data where each row is coded with a number (01, 02,
03... 10, 11, 12, etc.). Each row has a date associated with it.

I want to find out is the earliest start date of all the rows coded
"01", the earliest start date of all the rows coded "02", etc.

Does anyone know how this can be done?

(NOTE: My "Code" column is column C and is named "Code". My "Dates"
column is column H and is named "Dates".)

 

[Pete_UK]

You could list your codes somewhere - let's assume column M with "01"
in M1, "02" in M2 etc. Then in N1 you could apply this array* formula:

=MIN(IF(Code=M1,Dates,40000))

* As this is an array formula, once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly then Excel will wrap curly braces { }
around the formula - you must not type these yourself.

You can copy the formula down column N for as many codes as you have in
column M.

Hope this helps.

Pete

 

[JJ]

What does the 40000 represent? How would that change if I wanted to
find the latest date?

 

[Pete_UK]

Dates are stored internally in Excel as the number of days since a
reference date (1st Jan 1900), so really they are just integers.
Today's date is effectively 38853. Hence 40000 is much larger than
this, and so will never be included as the minimum. If you wanted the
latest date you would use MAX and replace the 40000 with 0, i.e.:

=MAX(IF(Code=M1,Dates,0))

This could go into cell O1, committed with CTRL-SHIFT-ENTER again, and
copied down.

Hope this helps.

Pete