Add 30 days based on another date

CellCharger · #1 07-18-2022, 06:20 PM

Hi everyone,

I looked on the internet for a simple solution, but I couldn't find any.
I have a form where the user selected "date created" of a record. I am looking for something to add 30 days based in the "due date" field based on the date entered in the "date created" field.

mark99k · #2 07-18-2022, 09:27 PM

Assuming you've set the created date to a string, say, CreatedDate:

Code:

DateAdd("d", 30, CreatedDate)

returns the date 30 days in the future.

CellCharger · #3 07-18-2022, 09:52 PM

Thank you Mark. I am sorry but I am not familiar with codes. Where and how do I add this code?
Also, the "date created" field is a popup calendar. Will it still work?

mark99k · #4 07-18-2022, 11:00 PM

Ah sorry. This forum is about VBA (that is, macros). Are you familiar with those? What I posted is a VBA expression that could become part of a macro written to do what you want. A more complete version is below. It assumes you start with your cursor in the table.

If you don't yet know how to write macros, this may not make much sense. I've only just returned to this forum after several years away, so I'm unsure whether there's a beginner-level posting about (or a pointer to a site for learning) the basics of VBA. Reply back if you need help finding one. The learning curve for VBA is steep but short.

Code:

Sub AddDueDateBelowCreateDate()
Dim rng As Range, tbl As Table
Set tbl = Selection.Tables(1)
Set rng = tbl.Cell(1, 2).Range
rng.MoveEnd wdCharacter, -1
tbl.Cell(2, 2).Range.Text = DateAdd("d", 30, rng.Text)
End Sub

On your second question, yes, this macro would work regardless of whether the date is regular text or a result from the calendar ('Date Picker') Content Control

CellCharger · #5 07-19-2022, 08:05 AM

Thanks Mark, I would say knowledge is limited but I understand the basic concepts.

The code works perfectly but when I used on my form, it did not, I guess because of the refence. If you can modify the code, I would really appreciate. I attached a blank form (in case you need to see it)

This is how it looks

macropod · #6 07-19-2022, 08:14 AM

Assuming you're using a date-picker content control, you could use a ContentControlOnExit macro in the 'ThisDocument' module of the document or its template, coded as:

Code:

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)
Application.ScreenUpdating = False
Dim Dt As Date, StrDt As String
With CCtrl
  If .Title <> "StartDate" Then Exit Sub
  If .ShowingPlaceholderText = True Then
    ActiveDocument.SelectContentControlsByTitle("OffsetDate")(1).Range.Text = ""
  Else
    StrDt = .Range.Text
    If IsDate(StrDt) Then
      Dt = CDate(StrDt)
    Else
      Dt = CDate(Split(StrDt, (Split(StrDt, " ")(0)))(1))
    End If
    ActiveDocument.SelectContentControlsByTitle("OffsetDate")(1).Range.Text = Format(Dt + 30, .DateDisplayFormat)
  End If
End With
Application.ScreenUpdating = True
End Sub

where 'StartDate' is the title of your date-picker content control and 'OffsetDate' is the title of a text content control used for the output. The code updates the 'OffsetDate' content control automatically when you exit the 'StartDate' content control.

CellCharger · #7 07-19-2022, 09:22 AM

Thank you macropod for your support.

Yes, "Date of Initiation" is a Date Picker Content Control

The field that is supposed to add 30 days from date of initiation is called "Due Date".

I am not sure what should be the "Due Date" field type. I added a Rich Text Content Control (I am not sure if this should be another field type). So I changed the code to this:

Code:

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)
Application.ScreenUpdating = False
Dim Dt As Date, StrDt As String
With CCtrl
  If .Title <> "Date of Initiation" Then Exit Sub
  If .ShowingPlaceholderText = True Then
    ActiveDocument.SelectContentControlsByTitle("Due Date")(1).Range.Text = ""
  Else
    StrDt = .Range.Text
    If IsDate(StrDt) Then
      Dt = CDate(StrDt)
    Else
      Dt = CDate(Split(StrDt, (Split(StrDt, " ")(0)))(1))
    End If
    ActiveDocument.SelectContentControlsByTitle("Due Date")(1).Range.Text = Format(Dt + 30, .DateDisplayFormat)
  End If
End With
Application.ScreenUpdating = True
End Sub

The problem now is that there is no macro to run. I am sorry If I missed something.

Also, can the due date field be automatically calculated without having to run a macro?

macropod · #8 07-19-2022, 04:40 PM

Quote:

Originally Posted by CellCharger

I am not sure what should be the "Due Date" field type. I added a Rich Text Content Control (I am not sure if this should be another field type).

As per the advice I gave:

Quote:

Originally Posted by macropod

where 'StartDate' is the title of your date-picker content control and 'OffsetDate' is the title of a text content control used for the output.

your "Due Date" (which is not a field in Word parlance) should be either a plain text content control or a rich text content control.

Quote:

Originally Posted by CellCharger

The problem now is that there is no macro to run. I am sorry If I missed something.

Did you add the macro to the 'ThisDocument' module of the document or its template? It won't work if you put it in a different module.

Quote:

Originally Posted by CellCharger

Also, can the due date field be automatically calculated without having to run a macro?

Not when using content controls. It is possible when using formfields in a protected document, but there is no date-picker formfield.

CellCharger · #9 07-20-2022, 06:43 AM

I inserted it in "thisdocument" but I still can't have it to work.

Regarding my last question about having the due date automatically add 30 days, I found a thread on this forum with a similar request and it does something very similar to what I need. The solution was provided by I think one of the admins. In the solution provided, the user selects from a drop down menu A, B or C and select date from a calendar and then "review on" is automatically calculated.

I am not sure what would be easier. Please let me know how to fix this.

macropod · #10 07-20-2022, 03:54 PM

Attaching a document designed for an entirely different purpose doesn't help resolve your issue. Without seeing what you've done with your document, it's impossible to know where you went wrong.

See attached.

CellCharger · #11 07-20-2022, 06:16 PM

Thank you so much macropod for your help. This is exactly what I needed.

I knew I most likely did something wrong but I couldn't know what it is. I searched for hours but I was unable to figure it out by myself.

Thank you again.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
If date is Current Month 1 or within 30 days	Sje	Excel	3	09-30-2019 10:40 PM
Create a calendar based on shifts not days	lwlewis367	Project	1	11-04-2016 09:59 AM
Date field - future date calculation + only business days	neon4	Word	7	01-21-2016 02:21 PM
adding days to a date	euterpia	Excel	1	01-18-2016 07:42 AM
Date Field to add 10 Days to Current Date	Erbwon	Word	6	11-12-2012 06:17 PM