+ Reply to Thread
Results 1 to 6 of 6

networkdays and calendar duration

  1. #1
    Registered User
    Join Date
    08-15-2016
    Location
    Minneapolis
    MS-Off Ver
    2007
    Posts
    11

    networkdays and calendar duration

    I am trying to create a simple Gantt chart that shows project milestones and durations.

    Using NETWORKDAYS does not compute the correct time for duration, since it skips some calendar days.

    However, if I simply subtract Start Date From End Date, I get 0 for tasks that only take one day (they begin and end on the same day). That method works correctly for all other days.

    Is there a way to simply count the correct number of calendar days (so that the gantt chart shows dates correctly), but still shows one-day tasks as one full day?

    Thanks.

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: networkdays and calendar duration

    Welcome to the forum Bob,

    How does time come into the equation? Do you record the time? Do you count workdays as 8 hours?

    We need a bit more information, and maybe a sample worksheet of dates with an indication of what the answers should be before giving you an answer.
    I hope this helps, please let me know!

    Regards,

    David

    Please remember, your request is clearer if you attach a sample workbook.
    - Reply, then Click on Go Advanced and click on the Paper Clip OR scroll down to Manage Attachments.

    If this has been of assistance, please advise.
    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


  3. #3
    Registered User
    Join Date
    08-15-2016
    Location
    Minneapolis
    MS-Off Ver
    2007
    Posts
    11

    Re: networkdays and calendar duration

    I have attached a sample. The problem is the Gantt chart.

    If I use EndDate - StartDate the duration is zero for a one-day task, which is not correct.

    But if I use NETWORKDAYS the duration is wrong because it is not calendar time.

    So I used a combination of the two, but the Gantt chart is still not correct.

    Thanks.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: networkdays and calendar duration

    Hi Bob,

    How about =IF($D2-$C2<=1,NETWORKDAYS($C2,$D2),$D2-$C2)?

    I hope this helps, please let me know!

    Regards,

    David


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

    Last edited by David A Coop; 08-16-2016 at 10:59 PM.

  5. #5
    Registered User
    Join Date
    08-15-2016
    Location
    Minneapolis
    MS-Off Ver
    2007
    Posts
    11

    Re: networkdays and calendar duration

    Thanks David, that worked. Too bad it is so difficult.

    I see in the newer versions there is a DAYS command. Maybe that would help.

  6. #6
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: networkdays and calendar duration

    Quote Originally Posted by bobelmore View Post
    I see in the newer versions there is a DAYS command. Maybe that would help.
    DAYS() was introduced with Excel 2013. I don't think it would solve your problem completely, however, because a start and end on the same day would still return 0, not the 1 you are looking for.

    DAC

+ 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: 5
    Last Post: 01-02-2020, 07:45 AM
  2. [SOLVED] Networkdays.intl vs networkdays
    By shukla.ankur281190 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-30-2015, 08:39 AM
  3. Calendar Duration Formula
    By chris8877 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-09-2014, 04:22 PM
  4. [SOLVED] Formula: To calculate duration between two times, if duration is over 6hours subtract 30mi
    By MattBarnes7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-11-2013, 03:22 PM
  5. Calendar functions - linking work activities with formatted dates to calendar
    By SKSS in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 05-14-2012, 06:38 PM
  6. Calendar VBA auto filling week and month based on calendar entry.
    By perrymagic in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2011, 02:00 PM
  7. Replies: 0
    Last Post: 03-27-2008, 04:36 PM

Tags for this Thread

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