calculate age between two dates

[Guest]

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.

 

[Guest]

Take a look at

http://www.cardaconsultants.com/en/msaccess.php?lang=en&id=0000000011#age

 

[Guest]

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

Take a look at

http://www.cardaconsultants.com/en/msaccess.php?lang=en&id=0000000011#age
--
Hope this helps,

Daniel P

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.

 

[Guest]

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

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

Take a look at

http://www.cardaconsultants.com/en/msaccess.php?lang=en&id=0000000011#age
--
Hope this helps,

Daniel P

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.

 

[John W. Vinson]

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]

 

[Guest]

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)

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]

 

[Guest]

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!!

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

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

Take a look at

http://www.cardaconsultants.com/en/msaccess.php?lang=en&id=0000000011#age
--
Hope this helps,

Daniel P





:

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.

 

[Douglas J. Steele]

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

(no e-mails, please!)

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)

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]

 

[Guest]

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)

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

(no e-mails, please!)

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)

On Mon, 30 Jul 2007 12:06:02 -0700, CBeavers

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]

 

[Douglas J. Steele]

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

(no e-mails, please!)

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)

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

(no e-mails, please!)

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)

:

On Mon, 30 Jul 2007 12:06:02 -0700, CBeavers

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]

 

[Guest]

Thank you! Thank you! Thank you!

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

(no e-mails, please!)

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)

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

(no e-mails, please!)


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)

:

On Mon, 30 Jul 2007 12:06:02 -0700, CBeavers

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]