Networkdays Function (Saturdays = Workdays)

Y

Yockbo

New Member
Joined
Jul 23, 2003
Messages
2
Hello,

I'll try and explain this same question three different ways...

If the 'networkdays' function INCLUDED (or counted, if you will) Saturdays as workdays, I would have my solution. :wink:

Or...

I want to use the networkdays function to count the number of workdays (Mon-Sat) between a start date and a end date. In my case a workday is defined as Monday through Sunday.

Finally...put a third way...

A1 contains a start date value, B1 contains a close date value. I need a function/macro that can tell me how many non SUNDAYS there are between these two dates.


Any thoughts on this...would be appreciated.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Here is a function that will calculate the number of SUNDAYS in that time range:

=INT((B1-A1)/7)+IF(WEEKDAY(B1)=1,1,IF(WEEKDAY(A1)=1,1,0))

So, in order to find the number of non-Sundays, simply subtract the two dates, add 1, then subtract the formula above. It looks a little messy, but works:

=B1-A1+1-INT((B1-A1)/7)+IF(WEEKDAY(B1)=1,1,IF(WEEKDAY(A1)=1,1,0))
 
Upvote 0
=SUMPRODUCT(--ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(A1&":"&B1))),{2,3,4,5,6,7},0)))

where A1 < B1, both housing dates of interest.
 
Upvote 0
Another non-volatile formula that works:

=B1-A1+1-(INT((B1-1)/7)-INT((A1-1)/7)+INT(WEEKDAY(A1-1)/7))

jmiskey, the following doesn't always work:
=B1-A1+1-INT((B1-A1)/7)+IF(WEEKDAY(B1)=1,1,IF(WEEKDAY(A1)=1,1,0))
Click to expand...
For example, if A1 = 8/02/03 and B1 = 9/01/03, then the formula returns 27 instead of 26, because

=INT((B1-A1)/7)+IF(WEEKDAY(B1)=1,1,IF(WEEKDAY(A1)=1,1,0)) returns 4, instead of 5.

--Tom
 
Upvote 0
My friend Yockbo asked if I could solve this.

Let me know if this works.

__________code__________

Public Function networkdays(date11, date12)
Dim wd1 As Integer
wd1 = Weekday(date11)
date11 = date11 + (8 - wd1)
networkdays = 6 * (Int((date12 - date11 + 1) / 7)) + _
((date12 - date11) Mod 7) * ((date12 - date11) Mod 7 <> 0) * ((date12 - date11 + 1) Mod 7 <> 0) _
+ (8 - wd1) + (wd1 = 1) '-1 for true, 0 for false
End Function

__________code__________

Jim
 
Upvote 0
OOPS!!!

I tested a bunch of values, but missed the problem rrdonutz found. Anyway, I would recommend Aladdin's solution. Much cleaner, and more importantly, it works correctly.
 
Upvote 0
Originally posted by jmiskey
I tested a bunch of values, but missed the problem rrdonutz found. Anyway, I would recommend Aladdin's solution. Much cleaner, and more importantly, it works correctly.
Click to expand...
Aladin's is a clever approach and perhaps more intuitive. But it uses 6 function calls AND the INDIRECT function. I don't see that it's superior to the formula I suggested, and in some cases, won't return a non-zero answer (see "Indirect" column below). I might be missing something, but what I've gleaned from this board is that most experts (and I don't put myself in that category) generally strive for fewer function calls, less complexity, as well as non-volatility, wherever possible.

Somewhat bemused,

Tom
CountWkdaysBet2Dates.xls
ABCDEF
1CountingNon-Sundays
2BeginDateEndDate"Direct""Indirect"Networkdays
308/02/200309/01/20032626--
401/01/198802/03/203514,74414,744--
512/13/205306/06/20797,9770--
603/18/200212/17/2004863863--
7CountingMondays-Fridays
808/02/200309/01/2003212121
901/01/198802/03/203512,28612,28612,286
1012/13/205306/06/20796,64706,647
1103/18/200212/17/2004720720720
Sheet1
 
Upvote 0
Tom,

I was just referring to my formula and Aladdin's, not yours. I see that your formula is very similar to mine. However, from past experience, whenever both Aladdin and I post different responses, his is almost always "less expensive" and more efficient than mine.

I do not fully comprehend all the factors that go into making a formula more expensive or more efficient, so I really can say either way which is better. Maybe he can comment on your solution compared to his?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Workdays Formula (Specific)
Replies
7
Views
492
felixstraube
F
S
  • Solved
=Workday, first day being weekend
Replies
4
Views
416
saboh12617
S
Z
  • Solved
add number of working days to a specific date (Start Date) to have the (End Date)
Replies
5
Views
373
Peter_SSs
Peter_SSs
B
  • Solved
Issue with finding time difference taking account of weekends and workdays.
2
Replies
15
Views
483
Joe4
Joe4
S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
336
Tetra201
Tetra201

Forum statistics

Threads
1,225,138
Messages
6,183,085
Members
453,146
Latest member
Lacey D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top