Exclude weekends

[Blunder1]

Hi,

Is there any code for VBA where it will count between to dates and excludes weekends? I looked around and it seems to use a function called 'NETWORKDAYS', but this appears to be something you need to set up within excel which i would prefer to avoid.

Thanks

Blunder1

 

[Trevor G]

There is a Formula called NetWorkDays if this isn't available when you look at the Functions you would need to add in the Analysis Pack which can be found under the Tools Menu and Add In if using 2003. Then when you look at the Functions you will find it has been added.

No VBA required.

 

[Blunder1]

Thank you, Trevor. Many people will use this macro, some with very little Excel skills. On this basis i need to do the count within VBA as oppose to functions.

Any help would be appreciated.

Thanks

 

[VoG]

You could use NETWORKDAYS in VBA like this

Sub test()
Dim d1 As Long, d2 As Long, diff As Long
d1 = DateSerial(2011, 8, 16)
d2 = DateSerial(2011, 8, 26)
diff = Evaluate("NETWORKDAYS(" & d1 & "," & d2 & ")")
MsgBox diff
End Sub

 

[MickG]

Perhaps you can use this:-
This code loops through dates in column "A", if Week day date found then the code places the word "Weekday" in column "B" and if weekend date then "WeekEnd" is displayed .

Dim Rng As Range, Dn As Range
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If Not Weekday(Dn) = 1 And Not Weekday(Dn) = 7 Then
        Dn.Offset(, 1) = "Weekday"
    Else
        Dn.Offset(, 1) = "Weekend"
    End If
Next Dn

Mick

 

[Blunder1]

Perfect, VoG, works like a charm!! Thank you

 

[gregtx81]

Is there a difference or some distinction b/w using the Evaluate method as opposed to using Application.WorksheetFunction.xxxx?

 

[VoG]

In Excel 2003 and earlier you have to use Evaluate with functions from the Analysis Toolpak like NETWORKDAYS.

In Excel 2007+ you can use WorksheetFunction.Networkdays