+ Reply to Thread
Results 1 to 4 of 4

From a list of dates that changes in length, make list with all middle dates

  1. #1
    Registered User
    Join Date
    11-16-2013
    Location
    Belfast
    MS-Off Ver
    Excel 2010
    Posts
    25

    From a list of dates that changes in length, make list with all middle dates

    I have a list of dates that changes in length. I want to make a list with those dates, excluding the first and last. How can I do this?

    For example, from this list:
    3/1/2011
    4/1/2011
    5/1/2011
    6/1/2011
    7/1/2011
    8/1/2011
    9/1/2011
    10/1/2011
    11/1/2011
    12/1/2011
    1/1/2012
    2/1/2012
    3/1/2012

    I want to create this list:
    4/1/2011
    5/1/2011
    6/1/2011
    7/1/2011
    8/1/2011
    9/1/2011
    10/1/2011
    11/1/2011
    12/1/2011
    1/1/2012
    2/1/2012

    THANK YOU!

  2. #2
    Forum Contributor vamosj's Avatar
    Join Date
    04-23-2004
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    294

    Re: From a list of dates that changes in length, make list with all middle dates

    Assuming first data list starts in cell A1 and formula list starts in B1

    Paste in B1 then copy down as needed.

    =IF(ROW(B1)-ROW($B$1)+2<COUNTA(A:A),INDEX(A:A,MATCH(SMALL(A:A,ROW(B1)-ROW($B$1)+2),A:A,0)),"")
    Janos S. Vamos
    Data Systems Technician/Fire Controlman PO1(SW/AW)
    US Navy Retired


    Remember, Record Macro can be your friend for figuring out solutions.

    Good or Bad, let me know how I did by clicking on the "Add Reputation" * just to the lower left of here. it only takes a few seconds to let someone know.

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: From a list of dates that changes in length, make list with all middle dates

    Or, assuming your original list in in A1:A13, enter this formula in your first cell (anywhere is fine) and copy down until you start to get blanks for the results:

    =IF(ROWS($1:1)>COUNT($A$1:$A$13)-2,"",INDEX($A$1:$A$13,ROWS($1:2)))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    11-16-2013
    Location
    Belfast
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: From a list of dates that changes in length, make list with all middle dates

    Thanks, everyone! Both worked.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trying to make a filter list based on dates
    By siukingyiu in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 01:40 PM
  2. Replies: 1
    Last Post: 04-19-2013, 02:25 AM
  3. [SOLVED] Highlight Group of Dates if Dates Match List of other Dates
    By martinpgibson in forum Excel General
    Replies: 5
    Last Post: 10-24-2012, 08:14 PM
  4. Trying to make a timeline from a list of dates using VLOOKUP
    By carpenoctom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-17-2011, 04:21 PM
  5. How do I make a list of dates in a header?
    By jacki_rl in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-23-2008, 01:09 PM

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