Calculate age from date of birth

A

aneasiertomorrow

Hi all

Firstly, apologies for posting twice but I suddenly realised the thread I
posted to was quite old so people might not spot it...

I want to add a Text Form Field to my form that displays someone's age in
years and months after they add their dob to another field. I found this
response from Graham Mayor and have manged to make it display in months
instead of years but don't now how to make that years and months. I know the
formula in Excel if that helps. Oh and the wopr site is down so I can't read
the suggested article/thread.

I'm a VBA newbie barely beyond copying & pasting code I find on these
discussion groups :)

Here's Graham's post:

Assuming the date is entered in field Text1, the following macro run on exit
from text 1 will put the age in field Text2

Sub CalculateDiff()
Dim sDate As String
Dim vDiff As Variant
Dim sResult As String

sDate = ActiveDocument.FormFields("Text1").Result
sDate = Format(sDate, "d m yy")
If sDate <> "" Then
If Not IsDate(sDate) Then
Debug.Print "Not a valid date!"
Else
vDiff = DateDiff("m", CDate(sDate), Format(Date, "d m yy"))
If vDiff Mod 12 >= 6 Then
sResult = CStr(CInt((vDiff / 12)) + 1)
MsgBox "Difference in years is almost " & sResult
Else
sResult = CStr(CInt(vDiff / 12))
ActiveDocument.FormFields("Text2").Result = sResult
End If

End If
End If
End Sub

You should also be able to do this with calculated fields, albeit it is far
more complicated and you will need the samples from
www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902



Thanks
Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

PowerPoint Live 2007 28-31 October in New Orleans www.pptlive.com
See you there
 
G

Graham Mayor

You could run a macro on exit from the form field - something along the
lines of -

Sub CalculateDiff()
Dim sDate As String
Dim vDiff As Variant
Dim sResult As String
Dim oFld As FormFields
Set oFld = ActiveDocument.FormFields

sDate = oFld("BirthDate").Result
If sDate <> "" Then
If Not IsDate(sDate) Then
MsgBox "Not a valid date!", vbCritical, "Error"
Else
vDiff = DateDiff("m", CDate(sDate), Format(Date, "d m yy"))
sResult = CStr(CInt(vDiff / 12))
oFld("Age").Result = "Age " & sResult & " years " & vDiff Mod 12 & "
months"
End If
End If
End Sub


This assumes that your DOB field is called BirthDate and is formatted as a
date field with the mask d M yy
and that the result goes in the form field Age which is a text field with
fillin not enabled.

Doing the same things with fields will give you

{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}}{Set
Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}{Set Days{=MOD(md+dd-bd,md) \#
0}}{IF{={IF{={BirthDate \@ "dddd"}}= "!*" 0 1}+{IF{={BirthDate \@ "MMMM"}}=
"!*" 0 1}+{ IF{ by }= "!*" 1 0 }+{IF{BirthDate \@ yyyyMMdd}<{DATE \@
yyyyMMdd} 0 1}}= 0 "{Years} Year{IF{Years}= 1 "" s}, {Months}
Month{IF{Months}= 1 "" s}" "Data entry error!"}}

Only this time the result is pasted in the document at the point of
insertion rather than in the second field.

The link (in the message you quoted) is currently down but I can send you a
document containing this field if you contact me via my web site link - as
reproducing it without error will have you tearing your hair :)

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
A

aneasiertomorrow

Perfect thankyou! And I even managed to iron out a few glitches (due to my
apparent inability to copy/paste).

Sorry about the double post - I guess you use a real newsreader & not the
web interface then :)

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

PowerPoint Live 2007 28-31 October in New Orleans www.pptlive.com
See you there
 
G

Graham Mayor

If you had a copy of macropod's code from before the web crash, there is a
bug in the field code
In the error-checking code towards the end of the field, you'll see:
{IF{a}= "!*" 1 0}+{IF{Delay}= "{Delay}" 0 1}
Change that to:
{IF{by}= "!*" 1 0}
 
M

macropod

Hi Lucy,

Here's the field coding for your age calculation. As coded, the field asks you to input a birthdate, which it validates, then
returns a response in the form of "If your Date of Birth was "d MMMM yyyy", then your age is "Years", "Months" and "Days"." You can
edit the response part of the code to change the output (eg to delete the days from the output if you don't want them).

{QUOTE
{ASK BirthDate "What is the Birthdate?"}
{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}}
{Set Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}
{Set Days{=MOD(md+dd-bd,md) \# 0}}
{IF{={IF{={BirthDate \@ "dddd"}}= "!*" 0 1}+{IF{={BirthDate \@ "MMMM"}}= "!*" 0 1}+{IF{by}= "!*" 1 0}+{IF{BirthDate \@
yyyyMMdd}<{DATE \@ yyyyMMdd} 0 1}}= 0 "If your Date of Birth was {Birthdate \@ "d MMMM yyyy"}, then your age is {Years}
Year{IF{Years}= 1 "" s}, {Months} Month{IF{Months}= 1 "" s} and {Days} Day{IF{Days}= 1 "" s}." "Data entry error!"}}

If you're using a document that will be protected for forms, and the birthdate is obtained from a formfield that sets a 'BirthDate'
bookmark and uses date validation (and has the 'calculate on exit' property set), you could reduce the code to:

{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}}
{Set Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}
{Set Days{=MOD(md+dd-bd,md) \# 0}}
"If your Date of Birth was {Birthdate \@ "d MMMM yyyy"}, then your age is {Years} Year{IF{Years}= 1 "" s}, {Months}
Month{IF{Months}= 1 "" s} and {Days} Day{IF{Days}= 1 "" s}."}

Note: The field braces in the above examples (ie "{ }") are created in pairs via Ctrl-F9 - you can't simply type them in or copy &
paste them direct from this post. Although you don't need the line breaks between the various 'SET' statements, they do make the
code easier to follow.

Cheers
 
A

aneasiertomorrow

Wow thanks!

I never knew field codes could get so complicated - I've only really dipped
my toe in so far (hey - people are impressed when you know the difference
between Date and CreateDate).

I'm going the macro route this time but will keep your code for future
reference. Maybe one day I'll even be able to decipher it ;-)

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

PowerPoint Live 2007 28-31 October in New Orleans www.pptlive.com
See you there



macropod said:
Hi Lucy,

Here's the field coding for your age calculation. As coded, the field asks you to input a birthdate, which it validates, then
returns a response in the form of "If your Date of Birth was "d MMMM yyyy", then your age is "Years", "Months" and "Days"." You can
edit the response part of the code to change the output (eg to delete the days from the output if you don't want them).

{QUOTE
{ASK BirthDate "What is the Birthdate?"}
{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}}
{Set Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}
{Set Days{=MOD(md+dd-bd,md) \# 0}}
{IF{={IF{={BirthDate \@ "dddd"}}= "!*" 0 1}+{IF{={BirthDate \@ "MMMM"}}= "!*" 0 1}+{IF{by}= "!*" 1 0}+{IF{BirthDate \@
yyyyMMdd}<{DATE \@ yyyyMMdd} 0 1}}= 0 "If your Date of Birth was {Birthdate \@ "d MMMM yyyy"}, then your age is {Years}
Year{IF{Years}= 1 "" s}, {Months} Month{IF{Months}= 1 "" s} and {Days} Day{IF{Days}= 1 "" s}." "Data entry error!"}}

If you're using a document that will be protected for forms, and the birthdate is obtained from a formfield that sets a 'BirthDate'
bookmark and uses date validation (and has the 'calculate on exit' property set), you could reduce the code to:

{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}}
{Set Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}
{Set Days{=MOD(md+dd-bd,md) \# 0}}
"If your Date of Birth was {Birthdate \@ "d MMMM yyyy"}, then your age is {Years} Year{IF{Years}= 1 "" s}, {Months}
Month{IF{Months}= 1 "" s} and {Days} Day{IF{Days}= 1 "" s}."}

Note: The field braces in the above examples (ie "{ }") are created in pairs via Ctrl-F9 - you can't simply type them in or copy &
paste them direct from this post. Although you don't need the line breaks between the various 'SET' statements, they do make the
code easier to follow.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

aneasiertomorrow said:
Perfect thankyou! And I even managed to iron out a few glitches (due to my
apparent inability to copy/paste).

Sorry about the double post - I guess you use a real newsreader & not the
web interface then :)

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

PowerPoint Live 2007 28-31 October in New Orleans www.pptlive.com
See you there
Click to expand...
Click to expand...
 
M

macropod

Just wait till you can access the Woody's site - this is one of the simpler examples ...

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

aneasiertomorrow said:
Wow thanks!

I never knew field codes could get so complicated - I've only really dipped
my toe in so far (hey - people are impressed when you know the difference
between Date and CreateDate).

I'm going the macro route this time but will keep your code for future
reference. Maybe one day I'll even be able to decipher it ;-)

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

PowerPoint Live 2007 28-31 October in New Orleans www.pptlive.com
See you there



macropod said:
Hi Lucy,

Here's the field coding for your age calculation. As coded, the field asks you to input a birthdate, which it validates, then
returns a response in the form of "If your Date of Birth was "d MMMM yyyy", then your age is "Years", "Months" and "Days"." You
can
edit the response part of the code to change the output (eg to delete the days from the output if you don't want them).

{QUOTE
{ASK BirthDate "What is the Birthdate?"}
{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}}
{Set Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}
{Set Days{=MOD(md+dd-bd,md) \# 0}}
{IF{={IF{={BirthDate \@ "dddd"}}= "!*" 0 1}+{IF{={BirthDate \@ "MMMM"}}= "!*" 0 1}+{IF{by}= "!*" 1 0}+{IF{BirthDate \@
yyyyMMdd}<{DATE \@ yyyyMMdd} 0 1}}= 0 "If your Date of Birth was {Birthdate \@ "d MMMM yyyy"}, then your age is {Years}
Year{IF{Years}= 1 "" s}, {Months} Month{IF{Months}= 1 "" s} and {Days} Day{IF{Days}= 1 "" s}." "Data entry error!"}}

If you're using a document that will be protected for forms, and the birthdate is obtained from a formfield that sets a
'BirthDate'
bookmark and uses date validation (and has the 'calculate on exit' property set), you could reduce the code to:

{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}}
{Set Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}
{Set Days{=MOD(md+dd-bd,md) \# 0}}
"If your Date of Birth was {Birthdate \@ "d MMMM yyyy"}, then your age is {Years} Year{IF{Years}= 1 "" s}, {Months}
Month{IF{Months}= 1 "" s} and {Days} Day{IF{Days}= 1 "" s}."}

Note: The field braces in the above examples (ie "{ }") are created in pairs via Ctrl-F9 - you can't simply type them in or copy
&
paste them direct from this post. Although you don't need the line breaks between the various 'SET' statements, they do make the
code easier to follow.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

aneasiertomorrow said:
Perfect thankyou! And I even managed to iron out a few glitches (due to my
apparent inability to copy/paste).

Sorry about the double post - I guess you use a real newsreader & not the
web interface then :)

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

PowerPoint Live 2007 28-31 October in New Orleans www.pptlive.com
See you there



:

You could run a macro on exit from the form field - something along the
lines of -

Sub CalculateDiff()
Dim sDate As String
Dim vDiff As Variant
Dim sResult As String
Dim oFld As FormFields
Set oFld = ActiveDocument.FormFields

sDate = oFld("BirthDate").Result
If sDate <> "" Then
If Not IsDate(sDate) Then
MsgBox "Not a valid date!", vbCritical, "Error"
Else
vDiff = DateDiff("m", CDate(sDate), Format(Date, "d m yy"))
sResult = CStr(CInt(vDiff / 12))
oFld("Age").Result = "Age " & sResult & " years " & vDiff Mod 12 & "
months"
End If
End If
End Sub


This assumes that your DOB field is called BirthDate and is formatted as a
date field with the mask d M yy
and that the result goes in the form field Age which is a text field with
fillin not enabled.

Doing the same things with fields will give you

{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}}{Set
Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}{Set Days{=MOD(md+dd-bd,md) \#
0}}{IF{={IF{={BirthDate \@ "dddd"}}= "!*" 0 1}+{IF{={BirthDate \@ "MMMM"}}=
"!*" 0 1}+{ IF{ by }= "!*" 1 0 }+{IF{BirthDate \@ yyyyMMdd}<{DATE \@
yyyyMMdd} 0 1}}= 0 "{Years} Year{IF{Years}= 1 "" s}, {Months}
Month{IF{Months}= 1 "" s}" "Data entry error!"}}

Only this time the result is pasted in the document at the point of
insertion rather than in the second field.

The link (in the message you quoted) is currently down but I can send you a
document containing this field if you contact me via my web site link - as
reproducing it without error will have you tearing your hair :)

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>



aneasiertomorrow wrote:
Hi all

Firstly, apologies for posting twice but I suddenly realised the
thread I posted to was quite old so people might not spot it...

I want to add a Text Form Field to my form that displays someone's
age in years and months after they add their dob to another field. I
found this response from Graham Mayor and have manged to make it
display in months instead of years but don't now how to make that
years and months. I know the formula in Excel if that helps. Oh and
the wopr site is down so I can't read the suggested article/thread.

I'm a VBA newbie barely beyond copying & pasting code I find on these
discussion groups :)

Here's Graham's post:

Assuming the date is entered in field Text1, the following macro run
on exit from text 1 will put the age in field Text2

Sub CalculateDiff()
Dim sDate As String
Dim vDiff As Variant
Dim sResult As String

sDate = ActiveDocument.FormFields("Text1").Result
sDate = Format(sDate, "d m yy")
If sDate <> "" Then
If Not IsDate(sDate) Then
Debug.Print "Not a valid date!"
Else
vDiff = DateDiff("m", CDate(sDate), Format(Date, "d m yy"))
If vDiff Mod 12 >= 6 Then
sResult = CStr(CInt((vDiff / 12)) + 1)
MsgBox "Difference in years is almost " & sResult
Else
sResult = CStr(CInt(vDiff / 12))
ActiveDocument.FormFields("Text2").Result = sResult
End If

End If
End If
End Sub

You should also be able to do this with calculated fields, albeit it
is far more complicated and you will need the samples from
www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902



Thanks
Lucy
Click to expand...
Click to expand...
Click to expand...
 
G

Graham Mayor

By arrangement with Macropod the documents are now available to download
from my web site http://www.gmayor.com/downloads.htm#Third_party

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Just wait till you can access the Woody's site - this is one of the
simpler examples ...
Cheers

aneasiertomorrow said:
Wow thanks!

I never knew field codes could get so complicated - I've only really
dipped my toe in so far (hey - people are impressed when you know
the difference between Date and CreateDate).

I'm going the macro route this time but will keep your code for
future reference. Maybe one day I'll even be able to decipher it ;-)

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

PowerPoint Live 2007 28-31 October in New Orleans www.pptlive.com
See you there



macropod said:
Hi Lucy,

Here's the field coding for your age calculation. As coded, the
field asks you to input a birthdate, which it validates, then
returns a response in the form of "If your Date of Birth was "d
MMMM yyyy", then your age is "Years", "Months" and "Days"." You can edit
the response part of the code to change the output (eg to
delete the days from the output if you don't want them). {QUOTE
{ASK BirthDate "What is the Birthdate?"}
{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}} {Set
Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}} {Set Days{=MOD(md+dd-bd,md)
\# 0}} {IF{={IF{={BirthDate \@ "dddd"}}= "!*" 0 1}+{IF{={BirthDate
\@ "MMMM"}}= "!*" 0 1}+{IF{by}= "!*" 1 0}+{IF{BirthDate \@
yyyyMMdd}<{DATE \@ yyyyMMdd} 0 1}}= 0 "If your Date of Birth was
{Birthdate \@ "d MMMM yyyy"}, then your age is {Years}
Year{IF{Years}= 1 "" s}, {Months} Month{IF{Months}= 1 "" s} and
{Days} Day{IF{Days}= 1 "" s}." "Data entry error!"}} If you're using a
document that will be protected for forms, and
the birthdate is obtained from a formfield that sets a 'BirthDate'
bookmark and uses date validation (and has the 'calculate on exit'
property set), you could reduce the code to: {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}} {Set
Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}} {Set Days{=MOD(md+dd-bd,md)
\# 0}} "If your Date of Birth was {Birthdate \@ "d MMMM yyyy"},
then your age is {Years} Year{IF{Years}= 1 "" s}, {Months}
Month{IF{Months}= 1 "" s} and {Days} Day{IF{Days}= 1 "" s}."} Note: The
field braces in the above examples (ie "{ }") are created
in pairs via Ctrl-F9 - you can't simply type them in or copy &
paste them direct from this post. Although you don't need the line
breaks between the various 'SET' statements, they do make the code
easier to follow. Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"aneasiertomorrow" <[email protected]>
wrote in message
Perfect thankyou! And I even managed to iron out a few glitches
(due to my apparent inability to copy/paste).

Sorry about the double post - I guess you use a real newsreader &
not the web interface then :)

Lucy
--
MOS Master Instructor
www.aneasiertomorrow.com.au

PowerPoint Live 2007 28-31 October in New Orleans www.pptlive.com
See you there



:

You could run a macro on exit from the form field - something
along the lines of -

Sub CalculateDiff()
Dim sDate As String
Dim vDiff As Variant
Dim sResult As String
Dim oFld As FormFields
Set oFld = ActiveDocument.FormFields

sDate = oFld("BirthDate").Result
If sDate <> "" Then
If Not IsDate(sDate) Then
MsgBox "Not a valid date!", vbCritical, "Error"
Else
vDiff = DateDiff("m", CDate(sDate), Format(Date, "d m yy"))
sResult = CStr(CInt(vDiff / 12))
oFld("Age").Result = "Age " & sResult & " years " & vDiff Mod
12 & " months"
End If
End If
End Sub


This assumes that your DOB field is called BirthDate and is
formatted as a date field with the mask d M yy
and that the result goes in the form field Age which is a text
field with fillin not enabled.

Doing the same things with fields will give you

{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}}{Set
Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}{Set
Days{=MOD(md+dd-bd,md) \# 0}}{IF{={IF{={BirthDate \@ "dddd"}}=
"!*" 0 1}+{IF{={BirthDate \@ "MMMM"}}= "!*" 0 1}+{ IF{ by }= "!*"
1 0 }+{IF{BirthDate \@ yyyyMMdd}<{DATE \@ yyyyMMdd} 0 1}}= 0
"{Years} Year{IF{Years}= 1 "" s}, {Months} Month{IF{Months}= 1 ""
s}" "Data entry error!"}} Only this time the result is pasted in the
document at the point
of insertion rather than in the second field.

The link (in the message you quoted) is currently down but I can
send you a document containing this field if you contact me via
my web site link - as reproducing it without error will have you
tearing your hair :) --
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>



aneasiertomorrow wrote:
Hi all

Firstly, apologies for posting twice but I suddenly realised the
thread I posted to was quite old so people might not spot it...

I want to add a Text Form Field to my form that displays
someone's age in years and months after they add their dob to
another field. I found this response from Graham Mayor and have
manged to make it display in months instead of years but don't
now how to make that years and months. I know the formula in
Excel if that helps. Oh and the wopr site is down so I can't
read the suggested article/thread. I'm a VBA newbie barely beyond
copying & pasting code I find on
these discussion groups :)

Here's Graham's post:

Assuming the date is entered in field Text1, the following macro
run on exit from text 1 will put the age in field Text2

Sub CalculateDiff()
Dim sDate As String
Dim vDiff As Variant
Dim sResult As String

sDate = ActiveDocument.FormFields("Text1").Result
sDate = Format(sDate, "d m yy")
If sDate <> "" Then
If Not IsDate(sDate) Then
Debug.Print "Not a valid date!"
Else
vDiff = DateDiff("m", CDate(sDate), Format(Date, "d m yy"))
If vDiff Mod 12 >= 6 Then
sResult = CStr(CInt((vDiff / 12)) + 1)
MsgBox "Difference in years is almost " & sResult
Else
sResult = CStr(CInt(vDiff / 12))
ActiveDocument.FormFields("Text2").Result = sResult
End If

End If
End If
End Sub

You should also be able to do this with calculated fields,
albeit it is far more complicated and you will need the samples
from www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902



Thanks
Lucy
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating the days in a month 3
Calculating Dates 3
Age 9
Calculating age from date of birth and ignoring if blank 3
basage modular 4
Function works except with January and February? 1
VBA code to call first date of the year and last date of the year 2
Calculating Age In VBA (Chip Pearson) 3

Top