getworkdays VBA

[GerryK]

Hi,

Function GetWorkDays(StartDate As Long, EndDate As Long)
As Long
' returns the count of days between StartDate - EndDate
minus Saturdays and Sundays
Dim d As Long, dCount As Long
For d = StartDate To EndDate
If WeekDay(d, vbMonday) < 6 Then
dCount = dCount + 1
End If
Next d
GetWorkDays = dCount
End Function

With a date in A2 and B2 and in C2 =GETWORKDAYS(A2:B2) I
do not get the expected result, rather get #value.

I've got the VBA in Sheet and inserted module?

Can someone tell why it is not working, (using 2000)?

 

[Frank Kabel]

Hi
try
GETWORKDAYS(A2,B2)

But besided that why not use NETWORKDAYS from the Analysis Toolpak
addin?

 

[GerryK]

Thanks Frank,
I stole this code from the excel tip page for someone who
cannot addin as they do not have a full install. Had to go
long for them.
I tried the : because the ; was wrong in the tip page but
not the , as needed.
gerry

 

[Daniel.M]

Hi GerryK,

1.
In C2:
=GETWORKDAYS(A2,B2)

Notice the "," between the 2 parameters.

2.
Function present in Excel Analysis Toolpak (ATP), like NetWorkDays(), already
does that and permits you to add a range of holidays.

3.
Your algorithm is not optimal, especially for long intervals between StartDate
and Enddate. There is no need to add each day: it's much better to calc total
days elapse and substract number of Saturdays and Sundays. Let me know if you're
interested.

4. You don't really need VBA to do what you want to do. Excel formulas are
faster.

Regards,

Daniel M.