[AccessD] Age calculation function

This function can be made even more generic by passing in the month, day,
year, and day fraction (or the day as a fraction 12/4/2003 6:00AM would be
12, 4.25, 2003).  This way, the function can handle years BC.

Just in case anyone cared.  :-)

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Bobby Heid
Sent: Friday, August 01, 2003 2:51 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Age calculation function


Here you go!

Public Function Greg2JD(ByVal strDate As String) As Double
Dim A    As Long
Dim B    As Long
Dim MM   As Long
Dim YY   As Long
Dim DD   As Single

   MM = Month(strDate)
   YY = Year(strDate)
   DD = Day(strDate)
   
   'note, you could pass the time of day in as a fraction of a day.
   'if you do so, simply add the fraction of the day to DD
   'As is, this code assumes 0 hour, i.e. if it is the 4th,
   'then it assumed to be the 4th at 12:00:00 AM
   'DD = DD + sDayFrac     'sDayFrac is the variable holding fraction of day

   If MM < 3 Then
      YY = YY - 1
      MM = MM + 12
      End If

   A = YY \ 100         'integer math
   B = 2 - A + A \ 4    'integer math

   Greg2JD = Int(365.25 * (YY + 4716)) + Int(30.6001 * (MM + 1)) + DD + B -
1524.5
   
End Function



To work out our example, I called it with:
Debug.Print (Greg2JD("02/18/2002") - Greg2JD("02/29/1988")) / 365.25

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mark
Whittinghill
Sent: Friday, August 01, 2003 2:40 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Age calculation function


Sure, I'd like to see it.

Mark Whittinghill
Symphony Information Services
612-333-1311
mwhittinghill at symphonyinfo.com
----- Original Message ----- 
From: "Bobby Heid" <bheid at appdevgrp.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Friday, August 01, 2003 1:37 PM
Subject: RE: [AccessD] Age calculation function


> Why not convert both to Julian dates and then divide by 365.25?  This will
> give you years as a decimal number.
>
> In your example:
>
> (jd("02/18/2002") - jd("02/29/1988")) / 365.2
> =(2452323.5 - 2447220.5)/365.25
> =5103 /365.25
> =13.9712525667351 years
>
> If anyone is interested in the code to do this, let me know and I'll post
> it.
>
>
> Bobby
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
> Sent: Friday, August 01, 2003 1:49 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Age calculation function
>
>
> Hi Mark
>
> >   Are you saying 14 and 31 are correct or incorrect?  I would think they
> are
> > correct.  If so, using DateDiff with "m" and dividing by 12 will work.
I
> > think you're saying those values are correct, but if not, I'm confused.
>
> No no, I say those values are correct.
> But I don't quite understand the month-thing ... it will fail in about
> 50% of any calculation as it doesn't take the day into account:
>
>   #02/29/1988#, #02/18/2002#
>   should return 13, not 14.
>
> I've heard of a couple celebrating their 25 year wedding day 24 years
> after their wedding because the wife couldn't count to 25. That is
> just fun - they can celebrate it once again at the real date - but for
> business applications dealing with pensions, salaries, holidays etc.
> getting the wrong age or employment period is mandatory and you have
> to use reliable calculation methods.
>
> /gustav
>
>
> >> You may wish to look up "Age Calc" in the archives around Okt. 2002.
> >>
> >> Many of the "smart" suggestions will fail for date intervals like:
> >>
> >>   #03/01/1988#, #03/01/2002#
> >>   is 14
> >>
> >>   #03/01/1968#, #02/28/2000#
> >>   is 31
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com