+ Reply to Thread
Results 1 to 3 of 3

Help: match date values between start/finish dates, then pull data from non-adjacent cells

  1. #1
    Registered User
    Join Date
    09-06-2018
    Location
    Nashville, Tennessee
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    1

    Help: match date values between start/finish dates, then pull data from non-adjacent cells

    Hello all! First post. Thanks in advance for replying.

    I have included an example spreadsheet.
    In it I have a list color orders in column A, and the dates those orders are due in column B.
    There are multiple orders for the same color.
    I want a way to count and list all the color orders, not including duplicate color matches, that are due in the next 7 days, and in the next 30 days.

    I came up with a formula to count just the dates that fall between a start and finish date, but it's not smart enough to know of duplicate color entries.
    Like in my example file, there should only be 15 unique colors with orders due in the next 7 days, but because of multiple same color orders, it lists 18. Likewise, there should only be 6 unique colors with orders in the next 30 days, but it lists 7.

    I was thinking it would be better to just pull the unique colors with in the time period to a list, then have a separate formula count the resulting list.
    But this is what I can not figure out how to do. Help me Gurus of the Excel formulas

    P.S. The dates in column B are not in an Excel recognized date format, so I have a second column (column C) with formulas to convert the text into Excel recognized data values.
    Attached Files Attached Files
    Last edited by JohnSkinner; 09-06-2018 at 05:56 PM. Reason: Adding attachement

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Help: match date values between start/finish dates, then pull data from non-adjacent c

    You could put this formula in D2:

    =IF(AND(C2>=$E$2,C2<=$F$2,COUNTIFS(A$2:A2,A2)=1),1,"")

    and copy it down. when you sum the resulting 1's you will get 15. If you change the $F$2 in the middle to $G$2 and copy that down (maybe in a different column), then you get a result of 21.

    Hope this helps.

    Pete

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Help: match date values between start/finish dates, then pull data from non-adjacent c

    Hi JohnSkinner. Welcome to the forum.

    If you would still like a list of unique colors in addition to the unique counts insert a new column E. Then try a variation on Pete's formula in D2 and fill across column E. You will be able to count as well as reference the numbers for the formula to list the colors.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in D1:E1 count those row numbers
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in the new G7 enter this, fill down and across column H until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

+ 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. Create a dynamic chart with a start and finish date in two cells
    By OillyBob55 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-13-2018, 12:30 AM
  2. Trying to pull out values from different start dates and end dates.
    By leena1126 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-28-2014, 10:05 PM
  3. [SOLVED] calculate the time elapsed between start and finish dates
    By johnandrews in forum Excel General
    Replies: 1
    Last Post: 12-13-2013, 08:17 AM
  4. Replies: 3
    Last Post: 10-23-2012, 09:04 PM
  5. Replies: 5
    Last Post: 06-30-2011, 03:26 PM
  6. Formulating Different Start/Finish Dates with Man Hours
    By dbaddorf in forum Excel General
    Replies: 5
    Last Post: 05-06-2008, 10:15 AM
  7. [SOLVED] How do I chart date ranges with varying start and finish dates?
    By projectplanner in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 05-01-2005, 07:06 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