How do I add 90 days to a date I've selected in a Date Picker Content control field?

The following version of the macro shows how you can allow the Date-Picker Content Control's date to have formats like 'ddd MMM d, YYYY' or 'dddd, d MMMM YYYY', for example, and how to lock the output content controls so that no-one can edit them. The code lines for those refinements should be readily apparent.

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)

Application.ScreenUpdating = False

Dim Rng2 As Range, Rng3 As Range, StrDt As String

With CCtrl

  If .Title <> "Date1" Then Exit Sub

  StrDt = .Range.Text

  If UBound(Split(StrDt, " ")) > 2 Then StrDt = Trim(Replace(StrDt, Split(StrDt, " ")(0), ""))

  ActiveDocument.SelectContentControlsByTitle("Date2")(1).LockContents = False

  ActiveDocument.SelectContentControlsByTitle("Date3")(1).LockContents = False

  Set Rng2 = ActiveDocument.SelectContentControlsByTitle("Date2")(1).Range

  Set Rng3 = ActiveDocument.SelectContentControlsByTitle("Date3")(1).Range

  If IsDate(StrDt) Then

    Rng2.Text = Format(CDate(StrDt) + 30, CCtrl.DateDisplayFormat)

    Rng3.Text = Format(CDate(StrDt) + 90, CCtrl.DateDisplayFormat)

  Else

    Rng2.Text = "": Rng3.Text = ""

    If .ShowingPlaceholderText = False Then MsgBox "Not a valid date!", vbExclamation

  End If

  ActiveDocument.SelectContentControlsByTitle("Date2")(1).LockContents = True

  ActiveDocument.SelectContentControlsByTitle("Date3")(1).LockContents = True

End With

Application.ScreenUpdating = True

End Sub

Additional logic could also be added to move the 30 & 90 days so that, if they fall on a weekend, the dates could be shifted to the preceding Friday or following Monday.

That's as simple as:

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)

Application.ScreenUpdating = False

Dim Rng2 As Range, Rng3 As Range

With CCtrl

  If .Title <> "Date1" Then Exit Sub

  Set Rng2 = ActiveDocument.SelectContentControlsByTitle("Date2")(1).Range

  Set Rng3 = ActiveDocument.SelectContentControlsByTitle("Date3")(1).Range

  If .ShowingPlaceholderText Then: Rng2.Text = "": Rng3.Text = "": Exit Sub

  If IsDate(.Range.Text) Then

    Rng2.Text = Format(CDate(.Range.Text) + 30, CCtrl.DateDisplayFormat)

    Rng3.Text = Format(CDate(.Range.Text) + 90, CCtrl.DateDisplayFormat)

  Else

    MsgBox "Not a valid date!", vbExclamation: Rng2.Text = "": Rng3.Text = ""

  End If

End With

Application.ScreenUpdating = True

End Sub

Note that the code now references two output text content controls, named Date2 (for 30 days) and Date3 (for 90 days), respectively.

As coded, the macro formats the two output text content controls with the same date format that your Date-Picker Content Control uses. If you want a different format (such as 'ddd, MMM d, YYYY'), replace CCtrl.DateDisplayFormat with "ddd, MMM d, YYYY".

If you want the calculated date to update automatically, you'll need a macro.

And, given that a macro is needed, the simplest approach would be to use a ContentControlOnExit macro like:

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)

Application.ScreenUpdating = False

Dim Rng As Range

With CCtrl

  If .Title <> "Date1" Then Exit Sub

  Set Rng = ActiveDocument.SelectContentControlsByTitle("Date2")(1).Range

  If .ShowingPlaceholderText Then: Rng.Text = "": Exit Sub

  If IsDate(.Range.Text) Then

    Rng.Text = Format(CDate(.Range.Text) + 90, CCtrl.DateDisplayFormat)

  Else

    MsgBox "Not a valid date!", vbExclamation: Rng.Text = ""

  End If

End With

Application.ScreenUpdating = True

End Sub

where the input Date-Picker Content Control is titled 'Date1' and for the output you use a text content control titled 'Date2'. As coded, the macro outputs a date 90 days in advance. The date offset is controlled by the '90' in the code.

The above ContentControlOnExit macro would be added to your document's 'This Document' code module, and the document would need to be saved in the docm file format.

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

One other method to have it repeat is to use a Mapped Content Control. Changes made in one spot are reflected in all. You can do this by repurposing the Publish Date control.

Repeating Data Using Document Property Content Controls and Other Mapped Content Controls

However, this does not work with Paul's macro and you would need to use a complex field instead.

THANK YOU! Absolutely amazing! It worked! I was also able to a date picker for 90 days out.

I did try to change the date formatting for all three fields (Date1 - original date, Date 2 - 30 days out, Date3 - 90 days out) to ddd, MMM d, yyyy that returned an error of invalid date. Any idea if it's possible to accomplish this with that format? Copilot recommended a code that then returned a type mismatch error and later checking the regional settings which I did but that didn't make a difference.

That would obviously be a nice to have be not as important as the dates which I now have. I can't believe it actually works!