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.

--
Message posted via http://www.officekb.com

On 1 jul, 20:17, "Darrell_Sarrasin via OfficeKB.com" <u33691@uwe
wrote:
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.

--
Message posted viahttp://www.officekb.com

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

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

On Jul 1, 2:17*pm, "Darrell_Sarrasin via OfficeKB.com" <u33691@uwe
wrote:
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.

--
Message posted viahttp://www.officekb.com

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

"Darrell_Sarrasin via OfficeKB.com" wrote:

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.

--
Message posted via http://www.officekb.com

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

--
__________________________________
HTH

Bob

"Mike H" wrote in message
...
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

"Darrell_Sarrasin via OfficeKB.com" wrote:

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.

--
Message posted via http://www.officekb.com

Bob,

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

Mike

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Mike H" wrote in message
...
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

"Darrell_Sarrasin via OfficeKB.com" wrote:

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.

--
Message posted via http://www.officekb.com

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" wrote:

Bob,

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

Mike

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Mike H" wrote in message
...
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

"Darrell_Sarrasin via OfficeKB.com" wrote:

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.

--
Message posted via http://www.officekb.com

I think you are right Mike. My standard formula for that is a lot longer, I
assumed there was a reason for that. I tested yours thinking it must have
constraints, but it must have been rubbish testing as on re-checking your
formula seems fine. My apologies.

--
__________________________________
HTH

Bob

"Mike H" wrote in message
...
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" wrote:

Bob,

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

Mike

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Mike H" wrote in message
...
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

"Darrell_Sarrasin via OfficeKB.com" wrote:

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.

--
Message posted via http://www.officekb.com