Networkdays function not working as expected

1. 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. 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

3. 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. Re: Networkdays function not working as expected

managed to submit a excel

5. 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

6. 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. 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. 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. 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. 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. 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. Re: Networkdays function not working as expected

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

13. 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.

14. 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. Re: Networkdays function not working as expected

hi jonmo1

yes that worked like a charm! thks

16. Re: Networkdays function not working as expected

You're welcome.

17. 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. Re: Networkdays function not working as expected

Try

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

19. 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. Re: Networkdays function not working as expected

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

21. Re: Networkdays function not working as expected

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)

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