+ Reply to Thread
Results 1 to 21 of 21

Networkdays function not working as expected

  1. #1
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Networkdays function not working as expected

    Hi Team

    I have activated the analysis tool pack addin and using the formula stated below.

    Please Login or Register  to view this content.
    But unfortunately its not showing the expected result.

    Note:

    A4 - Start Date
    B4 - End Date
    RDATA$AC$2:$AC$500 - contains the holiday and saturday sunday date to be excluded while calculating the difference between start and end date with time.

    For example if start time is 03/06/2014 10:13 and End Time is 10/06/2014 07:26, then the result should be 4 but I'm getting 6 as the result.

    Any help will be really helpful.

    Best
    Rem0

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Networkdays function not working as expected

    6 is the correct answer can you attach the sheet as we cant see what dates your removing for AC2:AC500. You dont need to put saturdays and sundays in NETWORKDAYS
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Re: Networkdays function not working as expected

    Thanks for the swift response!

    unfortunately i cant attach a spreadsheet now.

    To explain in detail the AC2:AC500 contain the saturday and sunday dates (7/06/2014 and 8/06/2014).

    From the above example my understanding is starting date is 3rd june and end date is 10th june, if we exclude those weekend 7th and 8th june then the result should be 4.

    But the formula works like taking the start date as 1 and end date as 1.

    Is there any way to calculate in way that for every 24 hours to be calculated as 1 day.

    Best
    Rem0

  4. #4
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Re: Networkdays function not working as expected

    managed to submit a excel
    Attached Files Attached Files

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Networkdays function not working as expected

    =(B4-A4)-SUM(INDEX((WEEKDAY(A4+ROW(INDIRECT("$1:$"&INT(B4)-INT(A4)+1))-1,2)>=6)*1,0))
    Try this
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Networkdays function not working as expected

    You dont need to have the weekends in your exclusions, you could do an if statement around it, to say if time of A1>00:00 then take off the date or work it out in hours Done that before, convaluted, but gives a nice result after tweaking.

  7. #7
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Re: Networkdays function not working as expected

    Thanks for the solution.

    The formula above with >=6 keeps the workdays as six right? i wanted to have a weekdays as 5.

    For the if formula inclusion yep will give it a shot and comeback to you.

  8. #8
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Re: Networkdays function not working as expected

    hi nflsales

    the formula you provided can u confirm you used the >=6 to check the workdays for weekdays as 6?

  9. #9
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Re: Networkdays function not working as expected

    hi nflsales

    i tried the formula amending the >=6 as >=5 with the start date as 13/06/2014 10:07 and end date as 13/06/2014 13:39.

    The result is -1 any idea why it is?

    Best
    Rem0

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Networkdays function not working as expected

    Why you changed the >=6 as >=5
    if you tried with my formula it will the result as 0.147222222221899

  11. #11
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Re: Networkdays function not working as expected

    i was under impression the value denotes the no of workdays per week and for me the weekdays is 5.

  12. #12
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Networkdays function not working as expected

    as per my formula 1 gives Monday, 2 gives Tuesday, 3 gives Wednesday like that

  13. #13
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Networkdays function not working as expected

    It seems the basic problem is that you want the Start and End dates to NOT be included in the count.
    Therefor you expect 4 as the result.

    But the Networkdays function actually DOES include the start and end dates in the count
    Therefor the result of the formula is 6.

    To make Networkdays NOT count the start/end dates, just Add 1 to Start, and Subtract 1 from End.
    =NETWORKDAYS(A4+1,B4-1,RDATA!AC2:AC71)


    Also, to repeat what others have said.
    You don't have to include weekend dates in your Holidays range.
    That's the whole purpose of the Networkdays function, to ignore weekends.


    Hope that helps.



    Note, do NOT just subtract 2 from the result of Networkdays
    =NETWORKDAYS(....)-2
    This will lead to erroneous results.
    Last edited by Jonmo1; 07-02-2014 at 08:53 AM.

  14. #14
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Re: Networkdays function not working as expected

    to brief about my requirement, i want to calculate the difference between start date and end date excluding the weekends as well as the holidays.

  15. #15
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Re: Networkdays function not working as expected

    hi jonmo1

    yes that worked like a charm! thks

  16. #16
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Networkdays function not working as expected

    You're welcome.

  17. #17
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Re: Networkdays function not working as expected

    Hi Jonmo1

    Seems some issues still occuring, check the formula with start date as 8/06/2014 10:04 and end date as 09/06/2014 11:28 the result is throwing as -1

  18. #18
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Networkdays function not working as expected

    Try

    =MAX(0,NETWORKDAYS(A4+1,B4-1,RDATA!AC2:AC71))

  19. #19
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Networkdays function not working as expected

    =NETWORKDAYS(A4,B4,RDATA!AC2:AC71)-IF(MOD(A4,1)<=MOD(B4,1),0,1)
    try this

  20. #20
    Forum Contributor
    Join Date
    06-18-2012
    Location
    Chennai, India
    MS-Off Ver
    Excel 2003, 2010
    Posts
    212

    Re: Networkdays function not working as expected

    the max function formula works as expected....thks jonmo1

  21. #21
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Networkdays function not working as expected

    You're welcome.

+ 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. Networkdays function not working?
    By sherringtonjr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-30-2013, 11:01 AM
  2. [SOLVED] IF function not working with dates and NETWORKDAYS
    By Hendar in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-08-2013, 06:49 AM
  3. =NETWORKDAYS not working as expected and as it once did
    By USAOz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-13-2006, 10:05 AM
  4. [SOLVED] Hyperlink Function not working as expected
    By Hari Prasadh in forum Excel General
    Replies: 2
    Last Post: 04-16-2005, 09:08 AM
  5. [SOLVED] Networkdays Function Not Working
    By Al H. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-11-2005, 11:06 AM

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