Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Formula To Return First and Last Air Dates

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Column A contains different commercial titles (Nike 1, Nike 2, Nike 3, etc.). Column B contains a single date that the commercial aired on. For example:

    A B
    Nike 1 01/02/02
    Nike 1 03/01/02
    Nike 1 02/26/02
    Nike 2 12/31/01
    Nike 2 5/14/01
    Nike 3 6/12/01
    Nike 3 12/13/01
    Nike 3 11/4/01

    How can I create a formula that will return a first air date and last air date for each commercial? I need the formula to look at all the dates in column B for each different commercial and return the earliest air date and the most recent air date.

    In the above example, the formula would return for "Nike 1" First Air Date - 1/2/02, Last Air Date - 3/1/02

    I could sort by A then by B and manually look up the first and last air dates but that would take too long considering there are thousands of dates to scroll through for each commercial.

    [ This Message was edited by: Harley on 2002-04-05 09:10 ]

    [ This Message was edited by: Harley on 2002-04-05 09:11 ]

    [ This Message was edited by: Harley on 2002-04-05 09:11 ]

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-05 09:09, Harley wrote:
    Column A contains different commercial titles (Nike 1, Nike 2, Nike 3, etc.). Column B contains a single date that the commercial aired on. For example:

    A B
    Nike 1 01/02/02
    Nike 1 03/01/02
    Nike 1 02/26/02
    Nike 2 12/31/01
    Nike 2 5/14/01
    Nike 3 6/12/01
    Nike 3 12/13/01
    Nike 3 11/4/01

    How can I create a formula that will return a first air date and last air date for each commercial? I need the formula to look at all the dates in column B for each different commercial and return the earliest air date and the most recent air date.

    In the above example, the formula would return for "Nike 1" First Air Date - 1/2/02, Last Air Date - 3/1/02

    I could sort by A then by B and manually look up the first and last air dates but that would take too long considering there are thousands of dates to scroll through for each commercial.

    [ This Message was edited by: Harley on 2002-04-05 09:10 ]

    [ This Message was edited by: Harley on 2002-04-05 09:11 ]

    [ This Message was edited by: Harley on 2002-04-05 09:11 ]
    Hi Harley,

    I used A1:B8 for your data supplied.

    In C1 I placed the text, "Nike 1"
    In C2, "Nike 2"
    In C3, "Nike 3"

    In D1 I placed the following array-entered formula

    =MIN(IF($A$1:$A$8=C1,$B$1:$B$8))

    You must enter this with Ctrl+Shift+Enter, rather than just Enter. You will see Excel generated braces {} surrounding the formula if done correctly.

    In E1, I array-entered the following

    =MAX(IF($A$1:$A$8=C1,$B$1:$B$8))

    I copied down the range to fill in the rest.

    Please adjust your range as needed.

    If you need this with your text in one cell, use something like:

    =C1& " first aired on " & TEXT(MIN(IF($A$1:$A$8=C1,$B$1:$B$8)),"d/m/yy") & ", last aired on " & TEXT(MAX(IF($A$1:$A$8=C1,$B$1:$B$8)),"d/m/yy")

    also array enetered.

    Bye,
    Jay

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,571
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-05 09:09, Harley wrote:
    Column A contains different commercial titles (Nike 1, Nike 2, Nike 3, etc.). Column B contains a single date that the commercial aired on. For example:

    A B
    Nike 1 01/02/02
    Nike 1 03/01/02
    Nike 1 02/26/02
    Nike 2 12/31/01
    Nike 2 5/14/01
    Nike 3 6/12/01
    Nike 3 12/13/01
    Nike 3 11/4/01

    How can I create a formula that will return a first air date and last air date for each commercial? I need the formula to look at all the dates in column B for each different commercial and return the earliest air date and the most recent air date.

    In the above example, the formula would return for "Nike 1" First Air Date - 1/2/02, Last Air Date - 3/1/02

    I could sort by A then by B and manually look up the first and last air dates but that would take too long considering there are thousands of dates to scroll through for each commercial.

    [ This Message was edited by: Harley on 2002-04-05 09:10 ]

    [ This Message was edited by: Harley on 2002-04-05 09:11 ]

    [ This Message was edited by: Harley on 2002-04-05 09:11 ]
    Lets say that A1:B9 houses your sample data including added headings:

    {"Titles","Comm Dates";
    "Nike 1",37258;
    "Nike 1",37316;
    "Nike 1",37313;
    "Nike 2",37256;
    "Nike 2",37025;
    "Nike 3",37054;
    "Nike 3",37238;
    "Nike 3",37199}

    The big numbers are just dates in the internal representation that Excel uses, so don't be alarmed.

    Prerequisite: The data must be sorted on column A.

    Create a unique list of titles (using e.g., Advanced Filter) in D from D2 on including the column heading "Titles".

    In D1 enter:

    =MATCH(9.99999999999999E+307,B:B)

    In E2:F2 enter:

    {"First Air Date","Last Air Date"}

    In E3 enter:

    =OFFSET($B$2,MATCH(D3,OFFSET($A$2,0,0,$D$1-1,1),0)-1,0,1)

    In F3 enter:

    =OFFSET($B$2,MATCH(D3,OFFSET($A$2,0,0,$D$1-1,1))-1,0,1)

    Select E3:F3 and copy down as far as needed.

    This is what you get to see in the results area, that is, D1:F5 regarding the sample data:

    {9,"","";
    "Titles","First Air Date","Last Air Date";
    "Nike 1",37258,37313;
    "Nike 2",37256,37025;
    "Nike 3",37054,37199}

    You don't need to adjust the formulas when you add to or delete from the data area titles and dates associated with them.

    Aladin

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay...that works great...this will save loads of time...thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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