#1
|
|||
|
|||
Age Calculation
When I open the document in https://www.msofficeforums.com/41523-post4.html by macropod, I can't see the formulas. What am i doing wrong? |
#2
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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? |
#4
|
||||
|
||||
Do you want the age in years, or do you need months and days as well?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Just years is fine. Thank you
|
#6
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Thank you Macropod!
|
#8
|
|||
|
|||
Age calculation in years, months, days
Quote:
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, |
#9
|
||||
|
||||
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 [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel calculation help | Purge07 | Excel | 4 | 05-20-2016 07:46 PM |
Help with a sum calculation please. | ballst | Excel | 4 | 06-04-2015 11:40 AM |
Formula Calculation | medavidcook | Excel | 2 | 03-21-2015 03:47 AM |
Please help with age calculation!!! | Microsoft Idiot | Word | 4 | 10-29-2012 05:54 AM |