Hi all,

I have two dates (30/03/2006 and 05/04/2006, for example)

How do I in VBA calculate the number of days between these two dates
BUT excluding the weekends?

Any Ideas Appreciated
TIA


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=530515

use the Networkdays function in the analysis toolpak.

--
Regards,
Tom Ogilvy



"gti_jobert" wrote:


Hi all,

I have two dates (30/03/2006 and 05/04/2006, for example)

How do I in VBA calculate the number of days between these two dates
BUT excluding the weekends?

Any Ideas Appreciated
TIA


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=530515

No matter guys, have came up with solution now;


Code:
--------------------

i = 0: V = 0
Do
dte = sDate + i
If (DatePart("w", dte, vbMonday) 5) Then
'do nothing
Else
V = V + 1
End If
i = i + 1
Loop Until Left(dte, 8) = Left(eDate, 8)

--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=530515

Hello,

I suggest not to take the analysis toolpak and not to count but to
calculate straightforward:

Option Explicit

Function daydiff(dtStart As Date, dtEnd As Date) As Long

daydiff = (dtEnd - Weekday(dtEnd, 2) + _
Weekday(dtStart, 2) - dtStart) _
/ 7 * 5 - min(5, Weekday(dtStart, 2)) _
+ min(5, Weekday(dtEnd, 2))

End Function

Function min(a As Long, b As Long) As Long
If a < b Then
min = a
Else
min = b
End If
End Function

Please have a look at http://www.sulprobil.com/html/date_formulas.html
and notice:
This formula is different from Excel's built-in function (analysis
toolpak). It counts from Startdate 24:00 until Enddate 24:00

HTH,
Bernd

Tom's answer is better.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"gti_jobert" wrote
in message ...

No matter guys, have came up with solution now;


Code:
--------------------

i = 0: V = 0
Do
dte = sDate + i
If (DatePart("w", dte, vbMonday) 5) Then
'do nothing
Else
V = V + 1
End If
i = i + 1
Loop Until Left(dte, 8) = Left(eDate, 8)

--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=530515

Hello Bob,

Depends, I think. Since we do not know the environment of the OP, I
only suggest...

Sometimes I like to use the analysis toolpak, sometimes not:
http://www.dicks-blog.com/archives/2...-addin-part-1/

Regards,
Bernd

But even if you don't want to use the ATP, looping through all the dates is
overkill.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
oups.com...
Hello Bob,

Depends, I think. Since we do not know the environment of the OP, I
only suggest...

Sometimes I like to use the analysis toolpak, sometimes not:

http://www.dicks-blog.com/archives/2...-addin-part-1/

Regards,
Bernd