tinyapps.org / blog

Microsoft Word: Calculate number of days between two dates #

In Excel, it's very simple to calculate the number of days between two dates by simply subtracting one cell from the other, e.g., entering =(B2-B1) in B3 below:

While Word supports basic formulas in tables (Table Tools → Layout → Data → Formula), date handling is unsupported; the formula above returns "0.0".

You can of course embed or link an Excel worksheet into a Word document, but there are issues with formatting, layout, and performance.

Field codes allow for calculating dates in Word, but aren't for the faint of heart. Paul Edstein/macropod's Microsoft Word Date Calculation Tutorial (mirror) is the definitive guide; here's a taste from the section entitled "Calculate the # Days Difference Between Two Dates":

{=
{SET a{=INT((14-{EndDate \@ M})/12)}}
{SET b{={EndDate \@ yyyy}+4800-a}}
{SET c{={EndDate \@ M}+12*a-3}}
{SET d{EndDate \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
-
{SET a{=INT((14-{StartDate \@ M})/12)}}
{SET b{={StartDate \@ yyyy}+4800-a}}
{SET c{={StartDate \@ M}+12*a-3}}
{SET d{StartDate \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
\# ,0}

More on fields:

• Introduction to FIELDS in MS Word
• Field codes: Date field
• Word field codes for everyone
• Dealing with Fields in Microsoft Word
• Word Fields: Part I
• Using { Fields } in Microsoft Word
• How Can I Put Calculated Dates in My Document Using Word?
• Using Date Fields in Microsoft Word
• Export field constructions as text
• Word Fields and Relative Paths to External Files

VBA's DateDiff function offers a more straightforward approach: simply insert two Date Picker Content Controls and one Rich Text Content Control (Developer → Controls), then add the following VBA code:

Sub CalculateDaysBetweenDates()
    Dim days As Integer, date1 As Date, date2 As Date
    date1 = ActiveDocument.ContentControls(1).Range
    date2 = ActiveDocument.ContentControls(2).Range
    days = DateDiff("d", date1, date2)
    ActiveDocument.ContentControls(3).Range = days
End Sub

Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
' Update days when focus leaves Content Control box
    Call CalculateDaysBetweenDates
End Sub

If there are additional fields or formulae in the table, add ActiveDocument.Fields.Update under Call CalculateDaysBetweenDates to update them as well.

Rather than referring to the Content Controls numerically based on their location in the document, you can specify their UIDs instead:

1. Click the desired Content Control to reveal the 3 dot tab to the left, then click on the tab.

2. Run this VBA code to display the CC's UID:

   Sub GetUniqueID()
   MsgBox Selection.ContentControls(1).ID
   End Sub

3. Replace the numeric CC reference in the CalculateDaysBetweenDates subroutine with the corresponding UID, e.g., ActiveDocument.ContentControls(1).Range → ActiveDocument.ContentControls("1247527479").Range

/windows | Aug 28, 2022

Subscribe or visit the archives.