Calculating Days Between Dates

[gti_jobert]

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]

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)

 

[Tom Ogilvy]

use the Networkdays function in the analysis toolpak.

 

[bplumhoff]

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

 

[Bob Phillips]

Tom's answer is better.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[bplumhoff]

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/2004/12/18/replacing-the-analysis-toolpak-addin-part-1/

Regards,
Bernd

 

[Bob Phillips]

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)