+ Reply to Thread
Results 1 to 5 of 5

Count work days between 2 dates but only between, not including start or end date

  1. #1
    Registered User
    Join Date
    07-24-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    2

    Count work days between 2 dates but only between, not including start or end date

    Hello,

    I am trying to calculate the difference between 2 work dates but not including the actual start or end date

    Mind you I am also not including Weekend or holidays so just M-F

    so for example if I have start date 6/15/17, and end date on 6/23/17 I would want my formula to give me an answer of 5, F, M, Tue, W, Th

    I do not want to include 6/15 which is a Thursday or 6/23 which is a Friday

    I have come up with this formula

    =IF(D11="","",NETWORKDAYS.INTL(C11, D11,,)-2)

    Where if D=11 is not populated it would return a blank cell, the two cells I am calculating the difference in dates would be C11 which is start date and D11 which is end date)

    I have added the -2 at the end which does remove the start and end date but the problem is if I have a date that starts on 6/15/17 and ends on 6/15/17 then it would return a negative number.

    if there is no actual way of just calculating the dates without the start and end date being included, is there a way I can have the formula return a 0 instead of a negative. I have also played around with the =MAX(0 option but I do not want to go that route because then it would make all cells 0 when in reality I want them blank unless the dates are filled in

    Sorry for my rant, I am just so confused.

    Thank you

  2. #2
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: Count work days between 2 dates but only between, not including start or end date

    This doesn't answer your question, but shouldn't it be -1 not -2? E.g. if your dates were 7/24/17 and 7/26/17, you would want an answer of "1" not "0" wouldn't you?

  3. #3
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: Count work days between 2 dates but only between, not including start or end date

    And hopefully this does answer your question. If you change the formula slightly you can set the answer to 0 whenever the two cells are equal.
    =IF(D11="","",IF(C11=D11,0,NETWORKDAYS.INTL(C11, D11,,)-2))

  4. #4
    Registered User
    Join Date
    07-24-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    2

    Re: Count work days between 2 dates but only between, not including start or end date

    regarding your first question, You would think that it would be -1, instead of -2, but everytime I put -1, it does not equal out, -2 usually solves it.

    Regarding the solution you provided, YES THANK YOU, that did the trick.

    Hersheys

  5. #5
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: Count work days between 2 dates but only between, not including start or end date

    Seems strange - you'd get -1 days when they are one day apart then.

    But I'm glad that worked for you

+ 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. Auto Populate a date only including work days
    By bennett493 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2016, 07:50 AM
  2. [SOLVED] To calculate number of days including start date
    By gautham_p in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 05-11-2016, 10:47 AM
  3. Calculate completion date from start date and duration - only work days
    By Robsheep in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-23-2015, 03:31 AM
  4. Counting number of days, including start date, without using +1
    By Crappy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-07-2014, 05:05 AM
  5. [SOLVED] Count Start and End Dates that Overlap Specified Date Range
    By DigDoug in forum Excel General
    Replies: 8
    Last Post: 06-11-2014, 09:46 AM
  6. Replies: 7
    Last Post: 03-26-2013, 08:32 AM
  7. Replies: 8
    Last Post: 05-16-2009, 03:49 AM

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