+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    JJ
    Guest

    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. #2
    Pete_UK
    Guest

    Re: 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. #3
    JJ
    Guest

    Re: 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. #4
    Pete_UK
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1