+ Reply to Thread
Results 1 to 7 of 7

List distinct years between two years.

  1. #1
    Registered User
    Join Date
    10-15-2014
    Location
    jcollett
    MS-Off Ver
    jcollett
    Posts
    2

    List distinct years between two years.

    I'm trying to figure out a formula that would allow me to take two year dates and list all years between them in two different formats.

    for instance in Columns C1 I have 1964 and then column D1 has 1966. Then from those years I would like E1 to display 1964 1965 1966 64 65 66

    Is this possible?

  2. #2
    Forum Contributor
    Join Date
    07-18-2012
    Location
    New York
    MS-Off Ver
    2016
    Posts
    184

    Re: List distinct years between two years.

    is the gap between years typically the same or could you have one row showing 1964 and 1966 and then another showing 1964 and 1974? If it's the same you could use something like this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If the gap changes you'd either have to add to the formula which could be very tedious or find another solution. There's probably a way to put this into a macro that would determine the gap and then produce the desired result but you'll have to wait for someone else to help you for that.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,692

    Re: List distinct years between two years.

    trisoldee has a good idea. Here is a User Defined Function (VBA) that has been floating around the forum for some time. It's docs say it was created by tigeravatar.

    Please Login or Register  to view this content.
    I used it this way:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You have the options of changing the delimiters and of course the nested formula. I used the literals here inside of ROW. INDIRECT can help you replace those with relative cell references.

    If you require more help with this please say so.

    Hope this helps.
    Last edited by FlameRetired; 10-15-2014 at 07:39 PM. Reason: afterthoughts

  4. #4
    Forum Contributor
    Join Date
    05-04-2014
    Location
    New Jersey, United States
    MS-Off Ver
    Excel 2010, 2013
    Posts
    120

    Re: List distinct years between two years.

    I'm pretty new to VBA, so maybe someone will find some things to improve upon in my code, but it seems to be working for me pretty well

    Please Login or Register  to view this content.

    Example
    If cell C1 has the year 1927 and cell D1 has the year 1934, then you can use the ConcatDates function above with the following syntax: =ConcatDates(C1,D1)
    The result will be 1927 1928 1929 1930 1931 1932 1933 1934 27 28 29 30 31 32 33 34



    Assumptions
    Neither cell that's being referenced can be blank
    Neither cell that's being referenced can be a non-number
    The end date must be greater than the start date

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,692

    Re: List distinct years between two years.

    And yet, another way could be to use the UDF in post3 with this nested formula and any delimiter of your choice including " ".

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It is an array formula that must be committed with Ctrl + Shift + Enter, and then drag the fill handle to copy down.

    Entered into C2 the results look like this:

    A
    B
    C
    1
    Begin Yr
    End Yr
    2
    1964
    1966
    1964 1965 1966 64 65 66
    3
    1998
    2002
    1998 1999 2000 2001 2002 98 99 00 01 02
    4


    ConcatAll is very flexible. I keep it in my library for solutions like this.

  6. #6
    Registered User
    Join Date
    10-15-2014
    Location
    jcollett
    MS-Off Ver
    jcollett
    Posts
    2

    Re: List distinct years between two years.

    Thanks everyone! Flame that worked perfect.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,692

    Re: List distinct years between two years.

    Glad it helped.

+ 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. [SOLVED] Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.
    By sharpmel in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-17-2013, 04:20 PM
  2. Replies: 2
    Last Post: 10-16-2012, 11:40 AM
  3. Replies: 4
    Last Post: 11-19-2010, 11:17 AM
  4. Replies: 15
    Last Post: 12-09-2006, 05:27 PM
  5. Replies: 1
    Last Post: 12-05-2005, 08:35 AM

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