Weekdays between two dates

[globetrot]

As the title suggests I am looking for some code to calculate the number of weekdays between two dates. I have tried datediff with the W argument but that didnt work.

Any ideas?

[malik641]

Found this function on the web at this site. The sub before is just me testing it.

[VBA] Option Explicit
Sub Testfunction()
Dim StartDate As Date
Dim EndDate As Date
StartDate = #5/25/2005#
EndDate = #5/30/2005#
MsgBox WorkingDays(StartDate, EndDate)
End Sub
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays
Dim intCount As Integer
StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above
intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate
Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount
Exit_WorkingDays:
Exit Function
Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function
[/VBA]

HTH

[Killian]

The "w" argument returns the number of weeks, just slightly differently to "ww", depending on which day startdate falls on.
With the posted function, there seems to be slight overkill with the use of the select statement - it's still useful to use it when testing ranges but the use in the error trap is a little strange.
Call me old fashioned, but I prefer functions to return values, rather than raise errors[VBA]Sub test()
MsgBox WorkingDays(#5/1/2005#, #6/1/2005#)
End Sub

'###############################################################
Function WorkingDays(StartDate As Date, EndDate As Date) As Long
'returns number of working days between two dates
'or -1 for any error
Dim i As Long, DayCount As Long

On Error GoTo errortrap
i = 0
Do While StartDate <= EndDate
Select Case Weekday(StartDate)
Case 2 To 6
i = i + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = i
Exit Function
errortrap:
WorkingDays = -1

End Function[/VBA]

[Bob Phillips]

As the title suggests I am looking for some code to calculate the number of weekdays between two dates. I have tried datediff with the W argument but that didnt work.

Any ideas?

=NETWORKDAYS(start,end)

[Sir Babydum GBE]

=NETWORKDAYS(start,end)

Does this formula require an add-in? If so, is there code that can be used to install the addin when the workbook is opened? (I have a similar query, you see.)

[Bob Phillips]

Does this formula require an add-in? If so, is there code that can be used to install the addin when the workbook is opened? (I have a similar query, you see.)

It requires the Analysis Toolpak add-in.

As to the econd part, it depends what you mean by install . If it's one of yours, and you mean it is defined to Tools>Add-ins, but just not checked, you just need to set Application.Addins("myAddin").installed = True.

[Sir Babydum GBE]

It requires the Analysis Toolpak add-in.

As to the econd part, it depends what you mean by install . If it's one of yours, and you mean it is defined to Tools>Add-ins, but just not checked, you just need to set Application.Addins("myAddin").installed = True.

Thanks.

Actually I just meant how you install the Analasis Toolpak add-in. I know how to do this manually, but would like to see it done automatically, so that other users don't have to bother when they open the w/b. They are a complaining lot, you see. Any instructions on how to do anything other than click on a cell, and they start to have nervous breakdowns!

Thanks again

[Zack Barresse]

They should only have to install it once. If you would like to do other things, it is the filename FUNCRES.XLA

[Sir Babydum GBE]

Cheers matey!

(Sorry to Hijack the thread, Globetrot!)

[lior03]

what about:
[VBA] Function dayDiff1(dte1 As Date, dte2 As Date, basis)
Select Case basis
Case "ww"
dayDiff1 = Abs(DateDiff("ww", dte1, dte2))
Case "d"
dayDiff1 = Abs(DateDiff("d", dte1, dte2))
Case " m"
dayDiff1 = Abs(DateDiff("m", dte1, dte2))
Case "q"
dayDiff1 = Abs(DateDiff("q", dte1, dte2))
End Select
End Function
[/VBA]
i hope you use it!!