+ Reply to Thread
Results 1 to 4 of 4

Count dates based on lookup Criteria

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    14

    Count dates based on lookup Criteria

    Hello everyone! I've run into a bit of a hold up. I'm trying to count the days of project duration on a table with repeated dates and multiple project numbers.

    What I'm looking for is a formula that will look at the date list for a specific WO# (or project id) and return what day number that was. What would also be useful is how to find the start in finished date (the min and max) based on the lookup...but i have a feeling one answer will lead me to the second.

    I've attached a simple file to help.
    formulating day count.xlsx

    Thanks for the help!

  2. #2
    Registered User
    Join Date
    12-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Count dates based on lookup Criteria

    Okay, I Figured it out I think...

    {=[@DATE]-MIN(IF([WO'#]=[@[WO'#]],[DATE]))}

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Count dates based on lookup Criteria

    Hi and welcome to the forum

    Sorry, but i have never seen an excel formula using @?

    I am looking at your file but im not sure what your expected outcome is. can you provide a few sample answers and decsribe how you arrived atthem please?

    edit: to find the earliest start date of a project, extract a list of unique project numbers and then use this against that list...

    =WEEKDAY(MIN(IF(B2:B46=B2,A3:A46,0)),1)
    change to max for latest date
    Last edited by FDibbins; 04-10-2013 at 03:28 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    12-06-2012
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Count dates based on lookup Criteria

    FDibbins,

    Thanks for the help! The @ used in the formula is used in a dynamic table formula. [@Date] points are the "Date" Column for the same row.

+ 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