calculate number of weekdays between dates in two form fields

[natanz]

I have a template project where a user will enter two dates in
formfields formatted as dates. I want a third field to calculate the
number of weekdays between those two dates.

I have started out with this, but it is not working.

Public Sub weekdaysbetween()
ActiveDocument.FormFields("weekdays").Result =
ActiveDocument.FormFields("date2").Result -
ActiveDocument.FormFields("date1").Result
End Sub

I know that this would only calculate the total difference in the
number of days (not only weekdays) if it was working, so that is the
first problem. The second problem is that is more basic, which is
that there is a type mismatch.

help please.

 

[natanz]

i have gotten a little further, but i still have a type mismatch, any
thoughts:

Public Sub numofdays()
Dim weekdays As Integer
weekdays = DateDiff(w, CDate(ActiveDocument.FormFields("date2").Result)
_ ,CDate(ActiveDocument.FormFields("date1").Result))
ActiveDocument.FormFields("total").Result = weekdays
End Sub

 

[Karl E. Peterson]

i have gotten a little further, but i still have a type mismatch, any
thoughts:

Public Sub numofdays()
Dim weekdays As Integer
weekdays = DateDiff(w,
CDate(ActiveDocument.FormFields("date2").Result) _
,CDate(ActiveDocument.FormFields("date1").Result))
ActiveDocument.FormFields("total").Result = weekdays
End Sub

There's two executable lines there, and you're not saying where the error is
occurring. I'm guessing the first? One problem that stands right out is
the first parameter to DateDiff -- it should be a string. Try "w" instead.

 

[natanz]

that helped. now i am on my way

 

[Greg]

natanz,

No saying that you are on the wrong track or that I am on the right
track. However, this seems to work:

Sub Test()
Dim pWeekday As Long, iDays As Long, i As Long, j As Long
Dim pDate1 As Date, pDate2 As Date, TmpDate As Date
On Error GoTo Handler
pDate1 = ActiveDocument.FormFields("Date1").Result 'Date 'Today's
date
pDate2 = ActiveDocument.FormFields("Date2").Result
Continue:
iDays = DateDiff("d", pDate1, pDate2)
For i = 1 To iDays
TmpDate = DateAdd("d", i, pDate1)
pWeekday = Weekday(TmpDate)
Select Case pWeekday
Case Is = 2, 3, 4, 5, 6
j = j + 1
Case Else
'Do Nothing
End Select
Next i
If pDate1 <> "0:00:00" And pDate2 <> "0:00:00" Then
ActiveDocument.FormFields("Weekdays").Result = j
Else
ActiveDocument.FormFields("Weekdays").Result = 0
End If
Exit Sub
Handler:
Resume Next
End Sub