Calculating the difference between two Date Picker Content Control

Topic

New Reply

Hi All,

I am going crazy trying to figure this out and have searched the internet all over, maybe I am asking in the wrong way when I search but I wanted to know if this is possible

I need to calculate the difference in weeks from two date picker content controls that the user will input into a different part of the document. If this is not possible, entering a start date and the amount of weeks and the end date automatically completing itself.

Thanks,

Reply | Quote

Replies

It’s quite simple, really. Suppose you have two content controls, titled ‘StartDate’ & ‘EndDate’, respectively. the following ContentControlOnExit macro will output the # whole weeks difference between them.

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim CCtrl As ContentControl, DtStart As Long, DtEnd As Long
With ContentControl
  If .Title = "StartDate" Then
    If IsDate(.Range.Text) Then
      DtStart = CDate(.Range.Text)
      With ActiveDocument.SelectContentControlsByTitle("EndDate")(1)
        If IsDate(.Range.Text) Then DtEnd = CDate(.Range.Text)
        MsgBox "Weeks difference = " & Int((DtEnd - DtStart) / 7)
      End With
    End If
  End If
  If .Title = "EndDate" Then
    If IsDate(.Range.Text) Then
      DtEnd = CDate(.Range.Text)
      With ActiveDocument.SelectContentControlsByTitle("StartDate")(1)
        If IsDate(.Range.Text) Then DtStart = CDate(.Range.Text)
        MsgBox "Weeks difference = " & Int((DtEnd - DtStart) / 7)
      End With
    End If
  End If
End With
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks macropod!

Just one thing it brings up a pop up box with the weeks difference, how to do I get the data to enter into another part of the document. Do I need to create a content control text box and name it and then amend this part of the code?

MsgBox “Weeks difference = ” & Int((DtEnd – DtStart) / 7)

Many thanks!

Reply | Quote

Sure you can send the output to the document, but you have to tell the code where. Possibilities include a bookmark, table cell or another content control. If you use a text content control titled ‘Weeks’ you could replace the message boxes with:
ActiveDocument.SelectContentControlsByTitle(“Weeks”)(1).Range.Text = Int((DtEnd – DtStart) / 7)

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Sure you can send the output to the document, but you have to tell the code where. Possibilities include a bookmark, table cell or another content control. If you use a text content control titled ‘Weeks’ you could replace the message boxes with:
ActiveDocument.SelectContentControlsByTitle(“Weeks”)(1).Range.Text = Int((DtEnd – DtStart) / 7)

but how do i run this code? I have a word document template where i use 2 date picker controls for starting and ending date and hoped there could be a way to calculate difference between those. however, even after copying this code i can’t find a way to run it. I am new to vba. Could you please help me?

Reply | Quote

See post #2, above.

For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm
For Mac macro installation & usage instructions, see: https://wordmvp.com/Mac/InstallMacro.html

The macro would be added to the ‘ThisDocument’ code module of the document or its template.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote