Count number of days

walidriachi · #1 07-22-2018, 07:20 AM

Hi

I am trying to create a leave form for my company im microsoft word and have created two date fields for commencing and end. I need to find a day to put the number of days between these dates excluding fridays in another text field is there a way to do that

gmaxey · #2 07-22-2018, 02:09 PM

Assuming leave start and leave end dates are entered in a datepicker type content control then something like this shoud work:

Code:

Sub Test()
Dim oCCS As ContentControl
Dim oCCE As ContentControl
Dim lngDays As Long
  Set oCCS = ActiveDocument.SelectContentControlsByTitle("Start").Item(1)
  Set oCCE = ActiveDocument.SelectContentControlsByTitle("End").Item(1)
  MsgBox fcnCalcDays(oCCS.Range, oCCE.Range)
lbl_Exit:
  Exit Sub
End Sub
Function fcnCalcDays(Date1 As Range, Date2 As Range) As Long
Dim lngDays As Long
Dim lngIndex As Long
Dim dTest As Date
  lngDays = 0
  dTest = Date1.Text
  For lngIndex = 1 To DateDiff("d", Date1.Text, Date2.Text)
    If Weekday(dTest, vbUseSystemDayOfWeek) <> 6 Then
      lngDays = lngDays + 1
    End If
    dTest = DateAdd("d", 1, dTest)
  Next
  fcnCalcDays = lngDays
lbl_Exit:
  Exit Function
End Function

gmayor · #3 07-22-2018, 08:46 PM

Expanding from Greg's code. If you add a plain text content control titled 'Days' then put the following in the ThisDocument module of the document, the calculation will be automatic.

Code:

Option Explicit

Private Sub Document_ContentControlOnEnter(ByVal ContentControl As ContentControl)
Dim oCCS As ContentControl
Dim oCCE As ContentControl
Dim oCCD As ContentControl
    Set oCCS = ActiveDocument.SelectContentControlsByTitle("Start").Item(1)
    Set oCCE = ActiveDocument.SelectContentControlsByTitle("End").Item(1)
    Set oCCD = ActiveDocument.SelectContentControlsByTitle("Days").Item(1)
    Select Case ContentControl.Title
        Case "Start"
            If oCCD.ShowingPlaceholderText = False And oCCE.ShowingPlaceholderText = False Then
                MsgBox "Make your change then click outside the field to update the day count."
            End If
        Case "End"
            If oCCE.ShowingPlaceholderText = False And oCCE.ShowingPlaceholderText = False Then
                MsgBox "Make your change then click outside the field to update the day count."
            End If
            If oCCS.ShowingPlaceholderText = True Then
                MsgBox "Complete the start date field before clicking here"
                GoTo lbl_Exit
            End If
        Case "Days"
            If oCCS.ShowingPlaceholderText = True Or oCCE.ShowingPlaceholderText = True Then
                MsgBox "Complete both date fields before clicking here"
                GoTo lbl_Exit
            Else
                oCCD.Range.Text = CStr(fcnCalcDays(oCCS.Range, oCCE.Range))
            End If
        Case Else
    End Select
lbl_Exit:
    Exit Sub
End Sub

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim oCCS As ContentControl
Dim oCCE As ContentControl
Dim oCCD As ContentControl
    Set oCCS = ActiveDocument.SelectContentControlsByTitle("Start").Item(1)
    Set oCCE = ActiveDocument.SelectContentControlsByTitle("End").Item(1)
    Set oCCD = ActiveDocument.SelectContentControlsByTitle("Days").Item(1)
    Select Case ContentControl.Title
        Case "Start"
            If oCCD.ShowingPlaceholderText = False And oCCE.ShowingPlaceholderText = False Then
                oCCD.Range.Text = CStr(fcnCalcDays(oCCS.Range, oCCE.Range))
            End If
            If Val(oCCE.Range.Text) < Val(oCCS.Range.Text) And _
               oCCE.ShowingPlaceholderText = False Then
                MsgBox "The start date is later than the end date?"
                oCCE.Range.Text = ""
                oCCD.Range.Text = ""
                GoTo lbl_Exit
            End If

        Case "End"
            If Val(oCCE.Range.Text) < Val(oCCS.Range.Text) And _
               oCCS.ShowingPlaceholderText = False Then
                MsgBox "The start date is later than the end date?"
                oCCE.Range.Text = ""
                oCCD.Range.Text = ""
                GoTo lbl_Exit
            End If
            If oCCD.ShowingPlaceholderText = False And oCCS.ShowingPlaceholderText = False Then
                oCCD.Range.Text = CStr(fcnCalcDays(oCCS.Range, oCCE.Range))
            End If
        Case Else
    End Select
lbl_Exit:
    Set oCCS = Nothing
    Set oCCE = Nothing
    Set oCCD = Nothing
    Exit Sub
End Sub

Function fcnCalcDays(Date1 As Range, Date2 As Range) As Long
Dim lngDays As Long
Dim lngIndex As Long
Dim dTest As Date
    lngDays = 0
    dTest = Date1.Text
    For lngIndex = 1 To DateDiff("d", Date1.Text, Date2.Text)
        If Weekday(dTest, vbUseSystemDayOfWeek) <> 6 Then
            lngDays = lngDays + 1
        End If
        dTest = DateAdd("d", 1, dTest)
    Next
    fcnCalcDays = lngDays
lbl_Exit:
    Set oCCS = Nothing
    Set oCCE = Nothing
    Set oCCD = Nothing
    Exit Function
End Function

Sub ClearDateFields()
Dim oCCS As ContentControl
Dim oCCE As ContentControl
Dim oCCD As ContentControl
    Set oCCS = ActiveDocument.SelectContentControlsByTitle("Start").Item(1)
    Set oCCE = ActiveDocument.SelectContentControlsByTitle("End").Item(1)
    Set oCCD = ActiveDocument.SelectContentControlsByTitle("Days").Item(1)
    oCCD.Range.Text = ""
    oCCE.Range.Text = ""
    oCCS.Range.Text = ""
lbl_Exit:
    Set oCCS = Nothing
    Set oCCE = Nothing
    Set oCCD = Nothing
    Exit Sub
End Sub

walidriachi · #4 07-22-2018, 10:30 PM

Thank you for your reply it seems i have missed something and both options are not working i have attached sample is it possible someone to insert the code so i can view where i went wrong

thanks a million

macropod · #5 07-22-2018, 11:09 PM

Try the attached.

I note your document had some macros referencing formfields, though none was present. You really shouldn't used formfields and content controls in the same document. They weren't designed to be used that way and doing so can cause problems.

gmayor · #6 07-23-2018, 12:13 AM

The attached includes the code I posted and I have changed the content controls to make it work. For inserting content controls see https://www.gmayor.com/insert_content_control_addin.htm

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Inserting a formula to count 30 days before and after a date in WORD table	bjh894	Word	9	05-18-2020 11:14 AM
count a number of cells based on the beginning of a order number	Kubi	Excel	2	08-06-2017 08:54 PM
Locking number of days in a project	Mdon1	Project	0	06-10-2017 02:40 AM
Assigning number of days	MarcF	Project	3	07-16-2016 10:44 AM
How to count even exact date 90 days	redza	Excel	1	09-17-2013 03:36 AM