Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating the difference between two Date Picker Content Control

    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,

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Jan 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •