CODEWITHSUNDEEP
vbams-wordms-office
Rick
Rick

Reputation: 2308

Word: Create formula to subtract dates in Word

I have a form in Word 2010 and am trying to enter two dates and get the difference in days. I tried creating a table with one row and three columns. Instead of dates I entered numbers in the first and second column and in the third I entered the formula =B1 - A1. With numbers it works, but not with dates.

Is it possible to enter two dates and get the difference in days in Word 2010? I know I can easily do this in Excel, but since the form is extensive I would like to try to do it in Word.

Upvotes: 1

Views: 4440

Answers (2)

user1379931
user1379931

Reputation:

There are at least 3 types of "inline forms" in Word 2010. All of them let you detect changes to fields in VBA.

  • If you are using the old ActiveX forms (not usually recommended these days) you can use the events associated with each control
  • If you are using the so-called "legacy forms" you can use the On Enter and On Exit macros for each field to invoke VBA
  • If you are using content controls, you can use the content control events.

Of those 3 options, only legacy forms are available on any version of Mac Word, and VBA is not avaiable on Mac Word 2008.

If you want to avoid VBA and use fields, there are two main considerations:

  1. There are no date functions or operators in the formula field { = }.
  2. The table cell references (cf. A1, B1 etc.) also only work with numbers, not dates.

You can work around point (1) by converting the dates to Julian Day Numbers, and using { = } to subtract the resulting numbers. See below for some suggested code.

You can avoid the problem in point (2) as long as you use form fields to enter the dates. Then you can use the form field's bookmark name to get the text and the date field switch "\@" to get the day. month and year numbers.

So let's suppose your two date form fields are called DA and DB. Then you can insert the following field codes at the point in the document where you want the result - notice that all the { } pairs have to be the special field code braces that you can enter using ctrl-F9.

{ SET MA { DA \@M } 
}{ SET CA { =INT((14-MA)/12) } 
}{ SET XD { ={ DA \@YYYY }-CA } 
}{ SET XC { =INT(XDA/100) } 
}{ SET XB { =XD-XC*100} 
}{ SET XA { =MA+12*CA-3 } 
}{ SET JA { =INT(146097*XC/4) + INT(36525*XB/100) + INT((153*XA+2)/5) + { DA \@D } + 1721119 } 
}{ SET MB { DB \@M } 
}{ SET CB { =INT((14-MB)/12) } 
}{ SET YD { ={ DB \@YYYY }-CB } 
}{ SET YC { =INT(YD/100) } 
}{ SET YB { =YD-YC*100 }
}{ SET YA { =MB+12*CB-3 } 
}{ SET JB { =INT(146097*YC/4) + INT(36525*YB/100) + INT((153*YA+2)/5) + { DB \@D } + 1721119 } 
}{ =JB-JA }

A few notes:

  • These field codes are based on the calculations provided here
  • The variable names have been changed a little (X1->XA etc.), and the calculations have been changed a little because there is no FLOOR function in the field language, and although there is a MOD function it is better avoided if you want your field coding to work internationally.
  • In this case, you could omit the two "-1721119" expressions.
  • You can remove most of the spaces in the above expressions if you prefer I have laid out the fields with "}{" at the beginnings of lines, because that approach hides the paragraph marks when the field results are displayed.
  • be aware that there can be a problem when you preview/print depending on Word's field updating settings.

There is a much more complete paper by "macropod" on using fields for date calculations. It uses a slightly different (and probably shorter) version of the algorithm, but the paper also provides algorithms for going in the other direction, and so on. I think you have to log in to get a copy from here , but you may be able to find it elsewhere.

Upvotes: 1

Pankaj Jaju
Pankaj Jaju

Reputation: 5471

If you are using VBA, then you can use DATEDIFF

Upvotes: 1

Related Questions