Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 15th 06, 11:27 PM posted to microsoft.public.excel.worksheet.functions
JJ
 
Posts: n/a
Default 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   Report Post  
Old May 16th 06, 12:36 AM posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default 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   Report Post  
Old May 16th 06, 06:54 AM posted to microsoft.public.excel.worksheet.functions
JJ
 
Posts: n/a
Default 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   Report Post  
Old May 16th 06, 09:36 AM posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I select from within a range of dates? Joe Leon Excel Discussion (Misc queries) 2 February 12th 06 03:41 PM
Formula to retrieve range of dates from a worksheet to calculate d accented Excel Worksheet Functions 0 January 10th 06 10:52 PM
Find a "date" in a column of dates in Excel 2000 JR Hester Excel Worksheet Functions 3 November 1st 05 10:17 PM
find a cells from a range of cell kelvintaycc Excel Worksheet Functions 2 April 2nd 05 07:20 PM
Use MATCH to find position of max in 2D range? Peter B Excel Worksheet Functions 4 October 28th 04 05:23 PM


All times are GMT +1. The time now is 09:21 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017