count work days

  • Thread starter Darrell_Sarrasin via OfficeKB.com
  • Start date
D

Darrell_Sarrasin via OfficeKB.com

I need a way of counting workdays between two dates. i can do it using the
NETWORKDAYS function but I know that not everyone has the add ons turned on
so its pointless as 100 people will be viewing this.

Any Help is greatly appreciated.
 
W

Wouter HM

I need a way of counting workdays between two dates.  i can do it usingthe
NETWORKDAYS function but I know that not everyone has the add ons turned on
so its pointless as 100 people will be viewing this.

Any Help is greatly appreciated.
Click to expand...

Hi Darrell,

I have had the same problem in the past using Excel 2003.

I created this combo of functions:

Function isWeekend(aDate As Date) As Boolean

If Weekday(aDate, vbSunday) = vbSunday Or Weekday(aDate, vbSunday)
= vbSaturday Then
isWeekend = True
Else
isWeekend = False
End If
End Function

Function countWorkdates(datFrom As Date, datUpto As Date) As Integer
Application.Volatile
Dim intCount As Integer
Dim datLoop As Date
'
For datLoop = datFrom To datUpto
If Not isWeekend(datLoop) Then intCount = intCount + 1
Next
countWorkdates = intCount
End Function

HTH,

Wouter
 
M

Mike H

Hi,

You can do it with Sumproduct and weekday which isn't part of ATP like this

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))

and if you want to include holidays try this

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C8,0))))

Where your holidays are in c1 - c8

Mike
 
B

Bob Phillips

If someone enter a weekend date as a holiday, that will deduct 1 for that
date as well.
 
J

JP

B1 contains 6/10/2009
C1 contains 7/1/2009

D1 contains the formula

=CEILING((C1-B1)-(((C1-B1)/7)*2),1)

This returns the value 15, which is the number of weekdays between the
two dates.

HTH,
JP
 
M

Mike H

Bob,

I think I agreed with you a bit too quickly. Take the dates 20/12/2009 -
31/12/2009
and 4 holiday dates 26/12, 27/12,28/12 & 21/12 in c1 - C4. Sumproduct
produces these 2 arrays. Because Sat and Sun are already evaluting as FALSE
the second array is irrelevent and only becomes relevant if the first array
evalueates as TRUE which it never will for a weekend date. The same as
networkdays (7 in this case) or am I missing something?

20/12/2009 0 1
21/12/2009 1 0
22/12/2009 1 1
23/12/2009 1 1
24/12/2009 1 1
25/12/2009 1 1
26/12/2009 0 0
27/12/2009 0 0
28/12/2009 1 0
29/12/2009 1 1
30/12/2009 1 1
31/12/2009 1 1

Mike

Mike H said:
Bob,

I know, do you know how to eliminate that problem?

Mike
Click to expand...
 
Top