Remember Me?

#1
May 15th 06, 11:27 PM posted to microsoft.public.excel.worksheet.functions
 JJ Posts: n/a
How do I find the earliest dates in a range of dates?

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".)

#2
May 16th 06, 12:36 AM posted to microsoft.public.excel.worksheet.functions
 Pete_UK Posts: n/a
How do I find the earliest dates in a range of dates?

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

#3
May 16th 06, 06:54 AM posted to microsoft.public.excel.worksheet.functions
 JJ Posts: n/a
How do I find the earliest dates in a range of dates?

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

#4
May 16th 06, 09:36 AM posted to microsoft.public.excel.worksheet.functions
 Pete_UK Posts: n/a
How do I find the earliest dates in a range of dates?

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Joe Leon Excel Discussion (Misc queries) 2 February 12th 06 02:41 PM accented Excel Worksheet Functions 0 January 10th 06 09:52 PM JR Hester Excel Worksheet Functions 3 November 1st 05 09:17 PM kelvintaycc Excel Worksheet Functions 2 April 2nd 05 07:20 PM Peter B Excel Worksheet Functions 4 October 28th 04 05:23 PM

All times are GMT +1. The time now is 12:43 PM.