I need to calculate the age of someone at the time that a test was taken. I
need the age to show years and percentage of year (3.9 years old at time of
test).

I'm putting this formula in a form in Access 2003

My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])

StuDOB = Student date of birth
DateTest = Date test was taken

I get the correct years, but I can't get the percentage in months.

Take a look at

http://www.cardaconsultants.com/en/m...0000000011#age
--
Hope this helps,

Daniel P





"CBeavers" wrote:

> I need to calculate the age of someone at the time that a test was taken. I
> need the age to show years and percentage of year (3.9 years old at time of
> test).
>
> I'm putting this formula in a form in Access 2003
>
> My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])
>
> StuDOB = Student date of birth
> DateTest = Date test was taken
>
> I get the correct years, but I can't get the percentage in months.
>

I'm using the expression builder in Access 2003. I tried that code and got an
error message that I was using invalid syntax

Thanks for your help though!
CBeavers

"Daniel" wrote:

> Take a look at
>
> http://www.cardaconsultants.com/en/m...0000000011#age
> --
> Hope this helps,
>
> Daniel P
>
>
>
>
>
> "CBeavers" wrote:
>
> > I need to calculate the age of someone at the time that a test was taken. I
> > need the age to show years and percentage of year (3.9 years old at time of
> > test).
> >
> > I'm putting this formula in a form in Access 2003
> >
> > My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])
> >
> > StuDOB = Student date of birth
> > DateTest = Date test was taken
> >
> > I get the correct years, but I can't get the percentage in months.
> >

This is the easiest age calculator I have come accross:

Public Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
Format(DateToday, "mmdd"), 1, 0)
End Function

--
Dave Hargis, Microsoft Access MVP


"CBeavers" wrote:

> I'm using the expression builder in Access 2003. I tried that code and got an
> error message that I was using invalid syntax
>
> Thanks for your help though!
> CBeavers
>
> "Daniel" wrote:
>
> > Take a look at
> >
> > http://www.cardaconsultants.com/en/m...0000000011#age
> > --
> > Hope this helps,
> >
> > Daniel P
> >
> >
> >
> >
> >
> > "CBeavers" wrote:
> >
> > > I need to calculate the age of someone at the time that a test was taken. I
> > > need the age to show years and percentage of year (3.9 years old at time of
> > > test).
> > >
> > > I'm putting this formula in a form in Access 2003
> > >
> > > My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])
> > >
> > > StuDOB = Student date of birth
> > > DateTest = Date test was taken
> > >
> > > I get the correct years, but I can't get the percentage in months.
> > >

On Mon, 30 Jul 2007 12:06:02 -0700, CBeavers
<(E-Mail Removed)> wrote:

>I need to calculate the age of someone at the time that a test was taken. I
>need the age to show years and percentage of year (3.9 years old at time of
>test).
>
>I'm putting this formula in a form in Access 2003
>
>My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])
>
>StuDOB = Student date of birth
>DateTest = Date test was taken
>
>I get the correct years, but I can't get the percentage in months.

Is 3.9 three years and nine months? or 3 years and 349 days (0.9 years)? Your
phrase "percentage in months" is worrisome...

To get years and fractional years, accurate to +1 in the first decimal place
over the span of a century (by ignoring leap years), you can use

Round(DateDiff("d", [StuDOB], [DateTest]) / 365., 1)


John W. Vinson [MVP]

I'm wanting to show 3 years 9 months as 3.9. Is that possible? I tried the
following in the expression builder Access 2003, and it gave me an invalid
syntax error. What am I doing wrong?


=DateDiff("yyyy", [StuDOB], [DateTest]), Round(DateDiff("m",[StuDOB],
[DateTest]) / 12., 1)

"John W. Vinson" wrote:

> On Mon, 30 Jul 2007 12:06:02 -0700, CBeavers
> <(E-Mail Removed)> wrote:
>
> >I need to calculate the age of someone at the time that a test was taken. I
> >need the age to show years and percentage of year (3.9 years old at time of
> >test).
> >
> >I'm putting this formula in a form in Access 2003
> >
> >My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])
> >
> >StuDOB = Student date of birth
> >DateTest = Date test was taken
> >
> >I get the correct years, but I can't get the percentage in months.
>
> Is 3.9 three years and nine months? or 3 years and 349 days (0.9 years)? Your
> phrase "percentage in months" is worrisome...
>
> To get years and fractional years, accurate to +1 in the first decimal place
> over the span of a century (by ignoring leap years), you can use
>
> Round(DateDiff("d", [StuDOB], [DateTest]) / 365., 1)
>
>
> John W. Vinson [MVP]
>

I'm putting this formula in a form in Access 2003 using the expression builder.

> > > > My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])
> > > >
> > > > StuDOB = Student date of birth
> > > > DateTest = Date test was taken

The Expression builder wants the field names in brackets [].

I put your expression in as

DateDiff("yyyy",[StuDOB],[DateTest]) - If(Format([StuDOB], "mmdd")>_
Format([DateTest], "mmdd"), 1, 0)
End function

but it gave me a syntax error message.

Please advise!! Thank you so much for your help!!


"Klatuu" wrote:

> This is the easiest age calculator I have come accross:
>
> Public Function Age(Bdate, DateToday) As Integer
> ' Returns the Age in years between 2 dates
> ' Doesn't handle negative date ranges i.e. Bdate > DateToday
>
> Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> Format(DateToday, "mmdd"), 1, 0)
> End Function
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "CBeavers" wrote:
>
> > I'm using the expression builder in Access 2003. I tried that code and got an
> > error message that I was using invalid syntax
> >
> > Thanks for your help though!
> > CBeavers
> >
> > "Daniel" wrote:
> >
> > > Take a look at
> > >
> > > http://www.cardaconsultants.com/en/m...0000000011#age
> > > --
> > > Hope this helps,
> > >
> > > Daniel P
> > >
> > >
> > >
> > >
> > >
> > > "CBeavers" wrote:
> > >
> > > > I need to calculate the age of someone at the time that a test was taken. I
> > > > need the age to show years and percentage of year (3.9 years old at time of
> > > > test).
> > > >
> > > > I'm putting this formula in a form in Access 2003
> > > >
> > > > My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])
> > > >
> > > > StuDOB = Student date of birth
> > > > DateTest = Date test was taken
> > > >
> > > > I get the correct years, but I can't get the percentage in months.
> > > >

DateDiff("m", [StuDOB], [DateTest]) will give you the total number of months
between the two dates. To only get the "left over" months, you could use
DateDiff("m", [StuDOB], [DateTest]) Mod 12.

However, adding 9 as .9 can be a bit problematic. If you were only adding
values between 1 and 9, you'd divide by 10 and add it to the number of
years. However, since you can also have values of 10, 11, and 12, that
approach won't work. It would be easier if you'd accept 3.09 for 3 years, 9
months, because then you could use

=DateDiff("yyyy", [StuDOB], [DateTest]) + _
Round((DateDiff("m",[StuDOB], [DateTest]) mod 12) / 100., 2)

Otherwise, you'll need something like:

=DateDiff("yyyy", [StuDOB], [DateTest]) + _
IIf((DateDiff("m",[StuDOB], [DateTest]) mod 12) < 10,
Round(DateDiff("m",[StuDOB], [DateTest]) mod 12 / 10, 1),
Round(DateDiff("m",[StuDOB], [DateTest]) mod 12 / 100, 2))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"CBeavers" <(E-Mail Removed)> wrote in message
news9F14810-F5C5-414C-95EF-(E-Mail Removed)...
> I'm wanting to show 3 years 9 months as 3.9. Is that possible? I tried the
> following in the expression builder Access 2003, and it gave me an invalid
> syntax error. What am I doing wrong?
>
>
> =DateDiff("yyyy", [StuDOB], [DateTest]), Round(DateDiff("m",[StuDOB],
> [DateTest]) / 12., 1)
>
> "John W. Vinson" wrote:
>
>> On Mon, 30 Jul 2007 12:06:02 -0700, CBeavers
>> <(E-Mail Removed)> wrote:
>>
>> >I need to calculate the age of someone at the time that a test was
>> >taken. I
>> >need the age to show years and percentage of year (3.9 years old at time
>> >of
>> >test).
>> >
>> >I'm putting this formula in a form in Access 2003
>> >
>> >My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])
>> >
>> >StuDOB = Student date of birth
>> >DateTest = Date test was taken
>> >
>> >I get the correct years, but I can't get the percentage in months.
>>
>> Is 3.9 three years and nine months? or 3 years and 349 days (0.9 years)?
>> Your
>> phrase "percentage in months" is worrisome...
>>
>> To get years and fractional years, accurate to +1 in the first decimal
>> place
>> over the span of a century (by ignoring leap years), you can use
>>
>> Round(DateDiff("d", [StuDOB], [DateTest]) / 365., 1)
>>
>>
>> John W. Vinson [MVP]
>>

I'm getting so close, but it's not quite right.

I am wanting to count up to 12 months.
I plugged 12/16/87 for the StuDOB (Student D.O.B) and 2/21/1991 for the
DateTest (Date of test)

But, I got back 3.98 instead of 3.02

=DateDiff("yyyy", [StuDOB], [DateTest]) + -
Round((DateDiff("m",[StuDOB], [DateTest]) mod 12) / 100., 2)

"Douglas J. Steele" wrote:

> DateDiff("m", [StuDOB], [DateTest]) will give you the total number of months
> between the two dates. To only get the "left over" months, you could use
> DateDiff("m", [StuDOB], [DateTest]) Mod 12.
>
> However, adding 9 as .9 can be a bit problematic. If you were only adding
> values between 1 and 9, you'd divide by 10 and add it to the number of
> years. However, since you can also have values of 10, 11, and 12, that
> approach won't work. It would be easier if you'd accept 3.09 for 3 years, 9
> months, because then you could use
>
> =DateDiff("yyyy", [StuDOB], [DateTest]) + _
> Round((DateDiff("m",[StuDOB], [DateTest]) mod 12) / 100., 2)
>
> Otherwise, you'll need something like:
>
> =DateDiff("yyyy", [StuDOB], [DateTest]) + _
> IIf((DateDiff("m",[StuDOB], [DateTest]) mod 12) < 10,
> Round(DateDiff("m",[StuDOB], [DateTest]) mod 12 / 10, 1),
> Round(DateDiff("m",[StuDOB], [DateTest]) mod 12 / 100, 2))
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "CBeavers" <(E-Mail Removed)> wrote in message
> news9F14810-F5C5-414C-95EF-(E-Mail Removed)...
> > I'm wanting to show 3 years 9 months as 3.9. Is that possible? I tried the
> > following in the expression builder Access 2003, and it gave me an invalid
> > syntax error. What am I doing wrong?
> >
> >
> > =DateDiff("yyyy", [StuDOB], [DateTest]), Round(DateDiff("m",[StuDOB],
> > [DateTest]) / 12., 1)
> >
> > "John W. Vinson" wrote:
> >
> >> On Mon, 30 Jul 2007 12:06:02 -0700, CBeavers
> >> <(E-Mail Removed)> wrote:
> >>
> >> >I need to calculate the age of someone at the time that a test was
> >> >taken. I
> >> >need the age to show years and percentage of year (3.9 years old at time
> >> >of
> >> >test).
> >> >
> >> >I'm putting this formula in a form in Access 2003
> >> >
> >> >My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])
> >> >
> >> >StuDOB = Student date of birth
> >> >DateTest = Date test was taken
> >> >
> >> >I get the correct years, but I can't get the percentage in months.
> >>
> >> Is 3.9 three years and nine months? or 3 years and 349 days (0.9 years)?
> >> Your
> >> phrase "percentage in months" is worrisome...
> >>
> >> To get years and fractional years, accurate to +1 in the first decimal
> >> place
> >> over the span of a century (by ignoring leap years), you can use
> >>
> >> Round(DateDiff("d", [StuDOB], [DateTest]) / 365., 1)
> >>
> >>
> >> John W. Vinson [MVP]
> >>
>
>
>

It looks as though you put a minus sign after the plus sign.

In what I had written, it was an underscore character (the line continuation
character). However, it does point out an issue with what I proposed, due to
how the DateDiff function works. When using yyyy as a parameter, DateDiff
returns how many year ends are passed. DateDiff("yyyy", #12/31/2006#,
#1/1/2007#) will return 1 year, despite the fact that it's only 1 day.

Try the following instead:

=(DateDiff("m", [StuDOB], [DateTest]) \ 12) + _
Round((DateDiff("m",[StuDOB], [DateTest]) mod 12) / 100., 2)

Note that's \, not /, in front of the 12 on the first line. If you're typing
it all on 1 line, ignore the underscore character.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"CBeavers" <(E-Mail Removed)> wrote in message
news:6A181DEC-1831-4A28-B616-(E-Mail Removed)...
> I'm getting so close, but it's not quite right.
>
> I am wanting to count up to 12 months.
> I plugged 12/16/87 for the StuDOB (Student D.O.B) and 2/21/1991 for the
> DateTest (Date of test)
>
> But, I got back 3.98 instead of 3.02
>
> =DateDiff("yyyy", [StuDOB], [DateTest]) + -
> Round((DateDiff("m",[StuDOB], [DateTest]) mod 12) / 100., 2)
>
> "Douglas J. Steele" wrote:
>
>> DateDiff("m", [StuDOB], [DateTest]) will give you the total number of
>> months
>> between the two dates. To only get the "left over" months, you could use
>> DateDiff("m", [StuDOB], [DateTest]) Mod 12.
>>
>> However, adding 9 as .9 can be a bit problematic. If you were only adding
>> values between 1 and 9, you'd divide by 10 and add it to the number of
>> years. However, since you can also have values of 10, 11, and 12, that
>> approach won't work. It would be easier if you'd accept 3.09 for 3 years,
>> 9
>> months, because then you could use
>>
>> =DateDiff("yyyy", [StuDOB], [DateTest]) + _
>> Round((DateDiff("m",[StuDOB], [DateTest]) mod 12) / 100., 2)
>>
>> Otherwise, you'll need something like:
>>
>> =DateDiff("yyyy", [StuDOB], [DateTest]) + _
>> IIf((DateDiff("m",[StuDOB], [DateTest]) mod 12) < 10,
>> Round(DateDiff("m",[StuDOB], [DateTest]) mod 12 / 10, 1),
>> Round(DateDiff("m",[StuDOB], [DateTest]) mod 12 / 100, 2))
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "CBeavers" <(E-Mail Removed)> wrote in message
>> news9F14810-F5C5-414C-95EF-(E-Mail Removed)...
>> > I'm wanting to show 3 years 9 months as 3.9. Is that possible? I tried
>> > the
>> > following in the expression builder Access 2003, and it gave me an
>> > invalid
>> > syntax error. What am I doing wrong?
>> >
>> >
>> > =DateDiff("yyyy", [StuDOB], [DateTest]), Round(DateDiff("m",[StuDOB],
>> > [DateTest]) / 12., 1)
>> >
>> > "John W. Vinson" wrote:
>> >
>> >> On Mon, 30 Jul 2007 12:06:02 -0700, CBeavers
>> >> <(E-Mail Removed)> wrote:
>> >>
>> >> >I need to calculate the age of someone at the time that a test was
>> >> >taken. I
>> >> >need the age to show years and percentage of year (3.9 years old at
>> >> >time
>> >> >of
>> >> >test).
>> >> >
>> >> >I'm putting this formula in a form in Access 2003
>> >> >
>> >> >My current calculation is DateDiff("yyyy",[StuDOB],[DateTest])
>> >> >
>> >> >StuDOB = Student date of birth
>> >> >DateTest = Date test was taken
>> >> >
>> >> >I get the correct years, but I can't get the percentage in months.
>> >>
>> >> Is 3.9 three years and nine months? or 3 years and 349 days (0.9
>> >> years)?
>> >> Your
>> >> phrase "percentage in months" is worrisome...
>> >>
>> >> To get years and fractional years, accurate to +1 in the first decimal
>> >> place
>> >> over the span of a century (by ignoring leap years), you can use
>> >>
>> >> Round(DateDiff("d", [StuDOB], [DateTest]) / 365., 1)
>> >>
>> >>
>> >> John W. Vinson [MVP]
>> >>
>>
>>
>>