Microsoft Office Forums Age calculation

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-27-2012, 09:56 PM
macropod's Avatar
macropod macropod is online now Age calculation Windows 7 64bit Age calculation Office 2010 32bit
Administrator
Age calculation
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,482
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default


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

When I open the above document by macropod, I can't see the formulas. What am i doing wrong?
Reply With Quote
  #3  
Old 10-31-2012, 12:47 AM
macropod's Avatar
macropod macropod is online now Age calculation Windows 7 64bit Age calculation Office 2010 32bit
Administrator
Age calculation
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,482
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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]
Reply With Quote
  #4  
Old 10-31-2012, 11:10 AM
lawlaw lawlaw is offline Age calculation Windows 7 64bit Age calculation Office 2007
Novice
 
Join Date: Oct 2012
Posts: 12
lawlaw is on a distinguished road
Default

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?
Reply With Quote
  #5  
Old 10-31-2012, 02:10 PM
macropod's Avatar
macropod macropod is online now Age calculation Windows 7 64bit Age calculation Office 2010 32bit
Administrator
Age calculation
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,482
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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]
Reply With Quote
  #6  
Old 10-31-2012, 02:54 PM
lawlaw lawlaw is offline Age calculation Windows 7 64bit Age calculation Office 2007
Novice
 
Join Date: Oct 2012
Posts: 12
lawlaw is on a distinguished road
Default

Just years is fine. Thank you
Reply With Quote
  #7  
Old 10-31-2012, 03:35 PM
macropod's Avatar
macropod macropod is online now Age calculation Windows 7 64bit Age calculation Office 2010 32bit
Administrator
Age calculation
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,482
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.

Your mailmerge age calculation is now ready to run.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #8  
Old 11-28-2012, 07:07 PM
lawlaw lawlaw is offline Age calculation Windows 7 64bit Age calculation Office 2007
Novice
 
Join Date: Oct 2012
Posts: 12
lawlaw is on a distinguished road
Default

Thank you Macropod!
Reply With Quote
  #9  
Old 06-07-2019, 05:46 AM
amanco amanco is offline Age calculation Windows 10 Age calculation Office 2016
Novice
 
Join Date: Jun 2019
Posts: 1
amanco is on a distinguished road
Default Age calculation in years, months, days

Quote:
Originally Posted by macropod View Post
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
File Type: docx Test2.docx (42.6 KB, 2 views)
Reply With Quote
  #10  
Old 06-07-2019, 06:22 AM
macropod's Avatar
macropod macropod is online now Age calculation Windows 7 64bit Age calculation Office 2010 32bit
Administrator
Age calculation
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,482
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Please help with age calculation!!! Microsoft Idiot Word 4 10-29-2012 05:54 AM
Calculation for my assignment important please ubns Excel 16 05-09-2012 02:08 AM
Age calculation Date Calculation Lights Excel 5 04-18-2012 04:31 AM
Age calculation Formula calculation danbl Excel 8 02-23-2012 04:35 AM
Age calculation Calculation within Cells manich1 Excel 2 12-07-2011 02:59 PM


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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft