+ Reply to Thread
Results 1 to 8 of 8

Formula for identifying start date and finish date and associated number of days

  1. #1
    Registered User
    Join Date
    11-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    31

    Formula for identifying start date and finish date and associated number of days

    I have a number of activities within a project with each activity having a start date and a finish date.

    What I would like is a function that looks at the 1st activity start date and the last activity finish date and convert this into the total amount of workdays taken to complete the project.

    Attached is a spreadsheet with generic data. Column A references the project with the cells I am looking to use highlighted in yellow columns B and C.

    Also I only need the dates, not the time so would like to know how to remove the time as well.
    Thanks in advance
    FBS
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    83,530

    Re: Formula for identifying start date and finish date and associated number of days

    As a starting point, in D2 drag copied down:

    =IF(A2=A3,"",NETWORKDAYS(DATEVALUE(TRIM(LEFT(INDEX(B$1:B2,MATCH(A2,A$1:A2,0)),10))),DATEVALUE(TRIM(LEFT(C2,10)))))

    What do you consider to be workdays?
    Last edited by AliGW; 08-06-2018 at 02:51 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    11-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Formula for identifying start date and finish date and associated number of days

    Thanks Ali. Works perfectly.

    Work days would be NETWORKDAYS (Monday to Friday)

    FBS

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    83,530

    Re: Formula for identifying start date and finish date and associated number of days

    You're welcome!

    I did notice one negative value - I suspect that was because of a mistake with sample data. The default NETWORKDAYS setting is for Saturday and Sunday to be the weekend, I believe, but you can change this by adding another argument. You can also define holidays, of course - if you are unsure how, Google NETWORKDAYS and NETWORKDAYS.INTL to find out.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Last edited by AliGW; 08-06-2018 at 03:52 AM.

  5. #5
    Registered User
    Join Date
    11-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Formula for identifying start date and finish date and associated number of days

    I struck a problem when I applied the formula. Not with the formula but with the data. It appears that the initial start date of the project may not necessarily be in the first row of the project and the final finish date in last row of the project.

    Is there any way that the formula can search for the project (ref no column A) then the earliest date for the project in column B and the latest date for the project in column C?

    Regards
    FBS

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    83,530

    Re: Formula for identifying start date and finish date and associated number of days

    I’d want to see some sample data before answering - truly representative this time, please!

  7. #7
    Registered User
    Join Date
    11-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Formula for identifying start date and finish date and associated number of days

    Have provided updated spreadsheet which highlights the issue I have.

    The projects highlighted in red text show where the earliest start date and the latest finish date are not necessarily in sequence as you run down the rows.

    Hence the need for a formula to identify the earliest and latest dates per project randomly placed in associated project rows.

    Thanks
    FBS
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    83,530

    Re: Formula for identifying start date and finish date and associated number of days

    See if this works for you:

    =IF(A2=A3,"",NETWORKDAYS(MIN(IF($A$2:$A$213=A2,DATEVALUE(TRIM(LEFT($B$2:$B$213,10))))),MAX(IF($A$2:$A$213=A2,DATEVALUE(TRIM(LEFT($C$2:$C$213,10)))))))

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, drag copy down.

+ 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. Replies: 7
    Last Post: 06-12-2018, 11:42 AM
  2. Count number of days from a start date to today BUT end at a certain date
    By delene_tabone in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-07-2018, 12:56 AM
  3. Replies: 3
    Last Post: 02-10-2016, 07:26 PM
  4. Does a task start or finish date fall within 3 month range of a specific date
    By jamesmcgallan in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-19-2015, 07:46 AM
  5. Calculate start date from finish date minus 14 working hours
    By PietBom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2014, 07:23 PM
  6. [SOLVED] Find date using start date and number of network days
    By v!ctor in forum Excel General
    Replies: 4
    Last Post: 07-06-2012, 05:27 PM
  7. Replies: 8
    Last Post: 03-29-2010, 09:20 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