Microsoft Office Forums > Word Age calculation
 Register FAQ Search Today's Posts Mark Forums Read

#1
10-27-2012, 09:56 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,482

To calculate an age in years, the field calculation can be quite simple. See attached.
Attached Files
 Fugitive Information Worksheet (Revised).docx (88.1 KB, 93 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
#2
10-31-2012, 12:33 AM
 lawlaw Windows 7 64bit Office 2007 Novice Join Date: Oct 2012 Posts: 12
Age calculation

When I open the above document by macropod, I can't see the formulas. What am i doing wrong?
#3
10-31-2012, 12:47 AM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,482

One wouldn't expect to see the formula - it's in a field code in the cell concerned. To see it, unprotect the document and press Alt-F9. In the cell concerned, you should see something like:
{QUOTE {IF{=Birthdate \@ YYYYMMDD}= 0 "" "{Set Years{=INT(({DATE \@ YYYYMMDD}-{REF Birthdate \@ YYYYMMDD})/10000)}}{Years} Year{IF{Years}= 1 "" s} }"}
It's a pretty simple formula, really.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
#4
10-31-2012, 11:10 AM
 lawlaw Windows 7 64bit Office 2007 Novice Join Date: Oct 2012 Posts: 12

Thanks Macropod. However, I don't believe the code provided who reach my goal, but I could be wrong.

I am an attorney and what I am doing is exporting information from my practice management software (PMS) into Word. In my PMS I have the child's birthdate and that merges into word as a merge field. To automate further, I want the merge document to auto-calculate the child's age based on the mergefield with the birthday in mm/dd/yyyy format. I feel the problem arises because the birthdate comes into word as numbers and slashes as, ex. 01/01/2001, and does not have mm dd yyyy attached to it. So I'm looking for a way to take the raw data "01/01/2001" and have word produce an Age based on that data. See below for an example:

3. Information About the Minor Child(ren) for whom I want the order changed:
Name: «MAT_Child_1_Name»
Birth Date: «MAT_Child_1_DOB»
Age: <age calculates here>

The «MAT_Child_1_DOB» is the merge field from my PMS as "01/01/2001" or whatever the birthdate is.

Is it possible to have the age auto-calculated here?
#5
10-31-2012, 02:10 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,482

Do you want the age in years, or do you need months and days as well?

PS: I'm splitting these posts off into a new thread in the mailmerge forum.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
#6
10-31-2012, 02:54 PM
 lawlaw Windows 7 64bit Office 2007 Novice Join Date: Oct 2012 Posts: 12

Just years is fine. Thank you
#7
10-31-2012, 03:35 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,482

In that case, copy the existing field code into your mailmerge main document, toggle the filed code display 'on' via Alt-F9, then make the following edits:

1. Just after the 'QUOTE' expressiion, press Ctrl-F9 to create a new field with a pair of field braces, thus:
{QUOTE { }{IF ...
2. Within the new field braces type 'SET Birthdate', thus:
{QUOTE {SET Birthdate }{IF ...
3. After 'Birthdate', press Ctrl-F9 to create a new field with a pair of field braces, thus:
{QUOTE {SET Birthdate { }}{IF ...
(note the space after 'Birthdate').
4. Within the new field braces type 'MERGEFIELD MAT_Child_1_DOB', thus:
{QUOTE {SET Birthdate {MERGEFIELD MAT_Child_1_DOB}}{IF ...

Your field code should now look like:
{QUOTE {SET Birthdate {MERGEFIELD MAT_Child_1_DOB}}{IF{=Birthdate \@ YYYYMMDD}= 0 "" "{Set Years{=INT(({DATE \@ YYYYMMDD}-{REF Birthdate \@ YYYYMMDD})/10000)}}{Years} Year{IF{Years}= 1 "" s} }"}

5. Press Alt-F9 to toggle the field code display.

__________________
Cheers,
Paul Edstein
[MS MVP - Word]
#8
11-28-2012, 07:07 PM
 lawlaw Windows 7 64bit Office 2007 Novice Join Date: Oct 2012 Posts: 12

Thank you Macropod!
#9
06-07-2019, 05:46 AM
 amanco Windows 10 Office 2016 Novice Join Date: Jun 2019 Posts: 1
Age calculation in years, months, days

Quote:
 Originally Posted by macropod To calculate an age in years, the field calculation can be quite simple. See attached.
Dear Macropod,

Can you please help me out, I tried copying the same code on *.docx (MS 2016) but it is not working. I attach the file.

Also is it possible please to show the results in (years, months and days)?

Thank you,
Attached Files
 Test2.docx (42.6 KB, 2 views)
#10
06-07-2019, 06:22 AM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,482

The problem is that you are using formfields and content controls in the same document. You shouldn't do that as they weren't designed to work together and trying to do so causes such problems.

As for including months and days, see Calculate the # Years Months & Days Difference Between Two Dates in my Microsoft Word Date Calculation Tutorial 'Sticky' thread at the top of the 'Word' forum:
https://www.msofficeforums.com/word/...-tutorial.html
Do read the document's introductory material.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Microsoft Idiot Word 4 10-29-2012 05:54 AM ubns Excel 16 05-09-2012 02:08 AM Lights Excel 5 04-18-2012 04:31 AM danbl Excel 8 02-23-2012 04:35 AM manich1 Excel 2 12-07-2011 02:59 PM

All times are GMT -7. The time now is 04:22 PM.

 -- Default Style -- Lightweight Contact Us - Privacy Statement - Top