MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### calculate age in years, months, days

• Follow

```In that order - years, months, days
=DateDiff("mmddyyyy",[BirthDate],Now())+(Format([BirthDate],"mmddyyyy")>Format(Now(),"mmddyyyy"))
I got the above from a post but when I try it I get #Error

Help
Janet
```
 0

```"mmddyyyy" is not valid for the Interval argument for the DateDiff() function!
You have to choose a single date component, and for this kind of thing it has
to the the lowest common denominator, Day.

You then have to parse the years/months/days out based on the days in a year,
days in a month and the remaining days. Because all months are not created
equal, some having 30 days, some having 31 days and February having varying
days, depending on whether or not it's a leap year, you'll have to compromise
and decide which on value to use for days in a month in order to do this.

--
There's ALWAYS more than one way to skin a cat!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

```
 0

```On Thu, 31 Jan 2008 13:16:03 -0800, Grams wrote:

> In that order - years, months, days
> =DateDiff("mmddyyyy",[BirthDate],Now())+(Format([BirthDate],"mmddyyyy")>Format(Now(),"mmddyyyy"))
> I got the above from a post but when I try it I get #Error
>
> Help
> Janet

Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
```
 0

```?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
Okay. This is what I got from the link you sent me to.  I don't want those
particular dates in there. My field is called [Birthdate]. So how would that
look calculating from today's date and using my fieldname.  I tried replacing
the 06/01/1998 with the field and 06/26/2002 with Now() and Date(), but got
errors on all tries.  Do I need the # signs in there?  It won't work no
matter what I try

Janet

"fredg" wrote:

> On Thu, 31 Jan 2008 13:16:03 -0800, Grams wrote:
>
> > In that order - years, months, days
> > =DateDiff("mmddyyyy",[BirthDate],Now())+(Format([BirthDate],"mmddyyyy")>Format(Now(),"mmddyyyy"))
> > I got the above from a post but when I try it I get #Error
> >
> > Help
> > Janet
>
> Check "A More Complete DateDiff Function" at
> http://www.accessmvp.com/djsteele/Diff2Dates.html
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>
```
 0

```On Thu, 31 Jan 2008 15:17:01 -0800, Grams wrote:

> ?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
> Okay. This is what I got from the link you sent me to.  I don't want those
> particular dates in there. My field is called [Birthdate]. So how would that
> look calculating from today's date and using my fieldname.  I tried replacing
> the 06/01/1998 with the field and 06/26/2002 with Now() and Date(), but got
> errors on all tries.  Do I need the # signs in there?  It won't work no
> matter what I try
>
> Janet
>
> "fredg" wrote:
>
>> On Thu, 31 Jan 2008 13:16:03 -0800, Grams wrote:
>>
>>> In that order - years, months, days
>>> =DateDiff("mmddyyyy",[BirthDate],Now())+(Format([BirthDate],"mmddyyyy")>Format(Now(),"mmddyyyy"))
>>> I got the above from a post but when I try it I get #Error
>>>
>>> Help
>>> Janet
>>
>> Check "A More Complete DateDiff Function" at
>> http://www.accessmvp.com/djsteele/Diff2Dates.html
>>
>> --
>> Fred
>> Please respond only to this newsgroup.
>> I do not reply to personal e-mail
>>

It does work.
In my Debug Window using your dates:
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days

ElapsedPeriod:Diff2Dates("ymd", [Birthdate], Date(), True)

If you wish to do this directly on a form or Report, then add an
unbound control.
Set it's control source to:
= Diff2Dates("ymd", [Birthdate], Date(), True)

Using my data, with a Birthdate value of 5/24/2007, my text control
value is "0 years 8 months 7 days" from then to today.

Change the True to False and the value is
8 months 7 days.

Open the module into which you pasted the function.
Click on Debug + Compile
It should not return any errors.
Make sure the name of the module into which you pasted the function
does not have the same name as the function (i.e. NOT Diff2Dates.
mdlDiff2Dates is OK).
Now try it again in a query.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
```
 0

4 Replies
1353 Views

Similiar Articles:

7/25/2012 9:57:46 AM