PDA

View Full Version : Weekdays between two dates



globetrot
11-28-2005, 06:25 AM
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
11-28-2005, 06:50 AM
Found this function on the web at this site (http://www.mvps.org/access/datetime/date0006.htm). The sub before is just me testing it.

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


:thumb HTH

Killian
11-28-2005, 07:56 AM
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 errorsSub 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

xld
11-28-2005, 08:04 AM
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
11-28-2005, 08:22 AM
=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.)

xld
11-28-2005, 09:18 AM
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
11-28-2005, 10:08 AM
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! :dunno

Thanks again

Zack Barresse
11-28-2005, 10:14 AM
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
11-28-2005, 10:41 AM
Cheers matey!

(Sorry to Hijack the thread, Globetrot!)

lior03
11-28-2005, 01:51 PM
what about:
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

i hope you use it!!