+ Reply to Thread
Results 1 to 4 of 4

Finding a date in a specific column surounded by other columns with similar dates

  1. #1
    Registered User
    Join Date
    10-21-2010
    Location
    Copenhagen
    MS-Off Ver
    Excel 2003
    Posts
    33

    Finding a date in a specific column surounded by other columns with similar dates

    Hi I have a worksheet as illustrated below:

    ___Date/Time________Date____Code_
    18-10-2011 07:00 _ 18-10-2011 _ 1
    18-10-2011 09:00_____________ 2
    18-10-2011 10:00_____________ 6
    19-10-2011 08:30 _ 19-10-2011 _ 5
    19-10-2011 09:45_____________ 4
    20-10-2011 12:30 _ 20-10-2011 _ 1
    20-10-2011 13:30_____________ 2
    20-10-2011 15:45_____________ 1
    21-10-2011 09:30 _ 21-10-2011 _ 3
    21-10-2011 12:30_____________ 2


    The first column (A) is used for time calculations

    Column B and C are used as a user interface where column A is hidden

    I want to find the first row in which the date 19-10-2011 occurs in either column A or B, is this possible?
    And is it possible to find the last row in which the date 20-10-2011 occurs in either column A or B?

    Thanks in advance ;-)

    Freakazoid
    Last edited by Freakazoid; 01-21-2012 at 05:28 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Finding a date in a specific column surounded by other columns with similar dates

    Try:

    For First Match:

    =MIN(MATCH(E1,INDEX(INT(A1:A10),0),0),MATCH(E1,B1:B10,0))

    For Last Match:

    =MAX(MATCH(2,INDEX(1/(INT(A1:A10)=E1),0)),MATCH(2,INDEX(1/(B1:B10=E1),0)))

    where E1 contains date of interest.

    if the dates in column B always align to first date in A, then you don't need to check B, do you?
    Last edited by NBVC; 01-18-2012 at 11:24 AM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    10-21-2010
    Location
    Copenhagen
    MS-Off Ver
    Excel 2003
    Posts
    33

    Re: Finding a date in a specific column surounded by other columns with similar dates

    Hi NVBC

    It does'nt work for me :-(

    I am programming in VBA didnt mention that before maybe that helps.

    Let me try to simplify my problem:

    I have worksheet containing several columns as indicated in the example in my first post. The only column I am interested in is column A (Example below)

    Example

    Column A
    18-11-2011 07:00
    18-11-2011 07:15
    18-11-2011 08:34
    18-11-2011 09:01
    18-11-2011 10:54
    18-11-2011 15:00
    19-11-2011 07:00
    19-11-2011 07:34
    19-11-2011 08:45
    19-11-2011 12:34
    20-11-2011 07:02
    20-11-2011 10:23
    20-11-2011 11:34
    21-11-2011 07:34
    .
    .
    .

    I want to identify the first row in which the a specific date Date1 occurs and the last row in which another date Date2 occurs.

    Again thanks in advance ;-)

    /Freakazoid

  4. #4
    Registered User
    Join Date
    10-21-2010
    Location
    Copenhagen
    MS-Off Ver
    Excel 2003
    Posts
    33

    Re: Finding a date in a specific column surounded by other columns with similar dates

    Solved it myself

    Please Login or Register  to view this content.
    Where Date3 is the date after Date2.

    /Freakazoid

+ 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