none
Automatic age calculation in Word RRS feed

  • Question

  • What I would like to do is have a field on a Word document where it will automatically subtract the date entered by the user from the current date and enter the results in years.  Basically, calculating an age.  If the user enters 8/10/1978, then it needs to automatically update an "Age" textbox with 32.  So, I tried to create a textbox for the date entry and gave it a bookmark name (lets say it's named birthdate).  Then I went to add a quick-parts field and set it to a formula, but if I enter (Date - birthdate) where date is the current date and birthdate is the bookmark field linked to the textbox, I get an error.

    Can someone help me with the coding for entering this information or inform me of another way to do it?  Again, it must automatically populate the Age field after a date is entered.

    Thank you in advance! 


    Friday, May 13, 2011 1:57 PM

Answers

  • Hi Hard-Working Programmer,

    The basic field code for doing this is:
    {QUOTE{SET by {BirthDate \@ yyyy}}
    {SET bm {BirthDate \@ M}}
    {SET bd {BirthDate \@ d}}
    {SET yy {DATE \@ yyyy}}
    {SET mm {DATE \@ M}}
    {SET dd {DATE \@ d}}
    {SET md{=IF((mm=2),28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),31-((mm=4)+(mm=6)+(mm=9)+(mm=11)))}}
    {Set Years{=yy-by-(mm<bm)-(mm=bm)*(dd<bd) \# 0}}
    "The age is {Years} Year{IF{Years}= 1 "" s}."}
    A formfield using a bookmark nmed 'BirthDate' is assumed. You can edit the output expression (on the last line) to delete any text you don't want.

    Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. Plus, the code is for an ordinary field - you don't use a formfield.

    For information on how to do just about everything else you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial, at:
    http://windowssecrets.com/forums/showthread.php/154368-Microsoft-Word-Date-Calculation-Tutorial
    or
    http://www.gmayor.com/downloads.htm#Third_party
    The above code is based on the item titled 'Interactively Calculate A Person’s Age'. Do read the document's introductory material.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Friday, May 13, 2011 9:57 PM

All replies

  • Hi Hard-Working Programmer,

    The basic field code for doing this is:
    {QUOTE{SET by {BirthDate \@ yyyy}}
    {SET bm {BirthDate \@ M}}
    {SET bd {BirthDate \@ d}}
    {SET yy {DATE \@ yyyy}}
    {SET mm {DATE \@ M}}
    {SET dd {DATE \@ d}}
    {SET md{=IF((mm=2),28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),31-((mm=4)+(mm=6)+(mm=9)+(mm=11)))}}
    {Set Years{=yy-by-(mm<bm)-(mm=bm)*(dd<bd) \# 0}}
    "The age is {Years} Year{IF{Years}= 1 "" s}."}
    A formfield using a bookmark nmed 'BirthDate' is assumed. You can edit the output expression (on the last line) to delete any text you don't want.

    Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. Plus, the code is for an ordinary field - you don't use a formfield.

    For information on how to do just about everything else you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial, at:
    http://windowssecrets.com/forums/showthread.php/154368-Microsoft-Word-Date-Calculation-Tutorial
    or
    http://www.gmayor.com/downloads.htm#Third_party
    The above code is based on the item titled 'Interactively Calculate A Person’s Age'. Do read the document's introductory material.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Friday, May 13, 2011 9:57 PM
  • Sorry for the late response.  Can you tell me where I would put the code you indicated above?  Am I putting it in as a macro and then setting the exit procedure of the "birthdate" formfield to the macro? Or is this the formula to put in for the "Age" field?
    Friday, May 20, 2011 3:28 PM
  • The formula is put in the "Age" field

    Here's an eleven year's old macro that could be used to do it (though I am sure that I could improve on it if I was to do the same thing now:

    ' a Macro to calculate the age in years, months and days
    ' based on a birthdate entered in cell A2 and insert the
    ' age in cell A3
    ' Macro created 16/9/00 by Doug Robbins
    '
    Dim Years As Long
    Dim Months As Long
    Dim Days As Long
    Dim Birthrange As Range
    Dim Birthday As Long
    Dim Day As Long
    Dim Age As String
    Dim Agedays As String
    Dim Yearnow As Long
    Dim Monthnow As Long
    Monthnow = Val(Format(Date, "m"))
    Yearnow = Val(Format(Date, "yyyy"))
    Dim mon$(12)
       mon$(1) = "January": mon$(2) = "February": mon$(3) = "March": mon$(4) = "April": mon$(5) = "May": mon$(6) = "June": mon$(7) = "July": mon$(8) = "August": mon$(9) = "September": mon$(10) = "October": mon$(11) = "November": mon$(12) = "December"
    Dim monthdays$(12)
       If ((Yearnow Mod 4 = 0 And Calyear Mod 400 = 0) Or (Calyear Mod 4 = 0 And Calyear Mod 100 <> 0)) Then
           monthdays$(1) = "31": monthdays$(2) = "29": monthdays$(3) = "31": monthdays$(4) = "30": monthdays$(5) = "31": monthdays$(6) = "30": monthdays$(7) = "31": monthdays$(8) = "31": monthdays$(9) = "30": monthdays$(10) = "31": monthdays$(11) = "30": monthdays$(12) = "31"
       Else
           monthdays$(1) = "31": monthdays$(2) = "28": monthdays$(3) = "31": monthdays$(4) = "30": monthdays$(5) = "31": monthdays$(6) = "30": monthdays$(7) = "31": monthdays$(8) = "31": monthdays$(9) = "30": monthdays$(10) = "31": monthdays$(11) = "30": monthdays$(12) = "31"
       End If
    Set Birthrange = ActiveDocument.Tables(1).Cell(1, 2).Range
    Birthrange.End = Birthrange.End - 1
    Birthdate = Birthrange
    Years = DateDiff("yyyy", Birthdate, Date)
    Months = DateDiff("m", Birthdate, Date) - Years * 12
    Birthday = Format(Birthdate, "d")
    Day = Format(Date, "d")
    Days = Day - Birthday
    If Days > 0 Then
       Agedays = " and " & Format(Days) & " days."
    ElseIf Days < 0 Then
       Agedays = " and " & Format(monthdays$(Monthnow) - Birthday + Day) & " days."
       Months = Months - 1
    Else
       Agedays = "."
    End If
    Age = Format(Years) & " years, " & Format(Months) & " Months" & Agedays
    ActiveDocument.Tables(1).Cell(1, 3).Range.Text = Age
     -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "Hard-Working Programmer" wrote in message news:52f32e6e-53c4-41ec-be5f-e1d4b518e6c8@communitybridge.codeplex.com...

    Sorry for the late response.  Can you tell me where I would put the code you indicated above?  Am I putting it in as a macro and then setting the exit procedure of the "birthdate" formfield to the macro? Or is this the formula to put in for the "Age" field?


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Friday, May 20, 2011 9:19 PM
  • Hi Hard-Working Programmer,

    As I said in my previous reply:
    Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. Plus, the code is for an ordinary field - you don't use a formfield.

    The code is field coding, not macro code and it goes in the body of the document wherever you want the output to appear. To use it, check the  'BirthDate' formfield's 'calculate on exit' property.


    Cheers
    Paul Edstein
    [MS MVP - Word]
    Friday, May 20, 2011 10:49 PM
  • Thank you so much, this worked perfectly!
    Tuesday, May 24, 2011 2:22 PM
  • Hi,

    Resurrecting an old thread.  I was able to get the field code programmed like you mentioned above.  However, I can't get it to auto update when i change the DOB in the form.  I have tried hitting F9 and a variety of other things.  I also am not sure if the document needs to be in "restricted editing" mode for the code to work?

    Basically, we have multiple patients where we enter a date of birth in a particular field in the top portion of the MS word document, and then need that date of birth field from the top portion of the form to transpose to an age in a paragraph below.

    Thanks for you help,

    -S

    Saturday, June 1, 2019 5:24 PM
  • Into what are you inputting the DOB? The calculation would only be automatic if you were entering it into a formfield (not a content control) in a document with 'filling in forms' protection enabled and your formfield's properties set to 'calculate on exit'.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Saturday, June 1, 2019 10:30 PM