Solved

How calculate age using two dates

Posted on 2014-01-07
20
722 Views
Last Modified: 2014-01-09
On a form I have two date fields (formatted as short date).  One is Date1, and the other is Date2.

Then there is a third field named Age.

Age needs to be calculated by using Date2 minus Date1.

For example. is Date1 is 1/1/2010 and Date2 is 1/7/2014 then the result in Age should be 4 (a whole number).

What would the code be for the afterupdate event of Date2?
0
Comment
Question by:SteveL13
  • 5
  • 5
  • 4
  • +3
20 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39762446
Try this:

Age = Abs(datediff("yyyy",Date2,Date1))

Open in new window

0
 
LVL 23

Accepted Solution

by:
Eirman earned 500 total points
ID: 39762460
You've described it yourself already ..... Date2 minus Date1
and use the INT function to change it to a whole number

Various methods are described here ...
http://www.fontstuff.com/excel/exltut01.htm
0
 

Author Comment

by:SteveL13
ID: 39762500
Neither suggestion is working.  

With Age = Abs(datediff("yyyy",Date2,Date1)) I get 114

With the other suggestion I get 115
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39762614
Double check that there are no typos in your dates, such as 2104 mistakenly used for 2014.

If you try this from the command line, it definitely gives you the correct results (4):

Debug.Print Abs(datediff("yyyy",#1/1/2010# ,#1/7/2014#))
0
 
LVL 23

Expert Comment

by:Eirman
ID: 39762636
Have a look at the attached sheet
Dates are in UK format
Ages.xls
0
 
LVL 23

Expert Comment

by:Eirman
ID: 39762721
The yellow section is Just in case you don't want to use today's date and you want a fixed date for calculation.
Ages.xls
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39762949
If we are talking about a person's age, usually we try to be more accurate.  So a person born in June doesn't reach his "age" until June of the year.  So from Jan to May, his age is x and from June to Dec, it is y.  Try these two functions.  You'll have to change the first to take a second argument rather than assuming today as the second date.

Public Function CalculateAge(DOB As Date) As Integer
    Dim WorkDate As Date
    Dim RawAge As Integer
    RawAge = DateDiff("yyyy", DOB, Date)
    WorkDate = DateSerial(Year(Date), month(DOB), Day(DOB))
    CalculateAge = RawAge + (Date < WorkDate) '(Date < WorkDate) = 0 or -1
End Function

Function CalculateLongAge(DOB As Date, CurDate As Date) As String
    Dim Y As Integer
    Dim M As Integer
    Dim D As Integer
    Dim Temp1 As Date
    Temp1 = DateSerial(Year(CurDate), month(DOB), Day(DOB))
    Y = Year(CurDate) - Year(DOB) + (Temp1 > CurDate)
    M = month(CurDate) - month(DOB) - (12 * (Temp1 > CurDate))
    D = Day(CurDate) - Day(DOB)
    If D < 0 Then
        M = M - 1
        D = Day(DateSerial(Year(CurDate), month(CurDate) + 1, 0)) + D + 1
    End If
    CalculateLongAge = Y & " years " & M & " months " & D & " days"
End Function

Open in new window

0
 

Author Comment

by:SteveL13
ID: 39762976
To PatHartman:

I'd like to try this but I'm not sure what to change or where.

My two fields are "DOB" and "DOP"  (Date of Birth and Date of Passing)

The third field I want calculated is "Age".

How do I code this?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39763014
The first function takes in only one input - the DOB and ASSUMES the other date will be the current date.  You would change the function to take two dates as input and any place in the code where the Date function is used, you would replace with the name of the DOP field.

The second function already takes two dates.

Public Function CalculateAge(DOB As Date, DOP As Date) As Integer
    Dim WorkDate As Date
    Dim RawAge As Integer
    RawAge = DateDiff("yyyy", DOB, DOP)
    WorkDate = DateSerial(Year(DOP), month(DOB), Day(DOB))
    CalculateAge = RawAge + (DOP< WorkDate) '(Date < WorkDate) = 0 or -1
End Function

Open in new window

So to use the first function.
Age = CalculateAge(DOB, DOP)

Open in new window


Note that there is no error checking in either function.  You should probably add some and decide what you need to do if one of the dates is missing.  You could verify that both dates are populated BEFORE calling the function and display an error message then OR you could verify that both dates are present inside the function.  That makes the function a little less flexible since what ever calls it will have to know how to deal with the error it raises.
0
 
LVL 23

Expert Comment

by:Eirman
ID: 39763712
Sorry .. I misread the topic area
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 61

Expert Comment

by:mbizup
ID: 39763864
Interesting.  I thought I had seen Excel as a second topic on this question initially too.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39764438
You have to use DateAdd to get it right for any given combo of dates:
Public Function Years( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByVal booLinear As Boolean) _
  As Integer

' Returns the difference in full years between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of year counts.
' For a given datDate1, if datDate2 is decreased step wise one year from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().

' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
'
' 2000-11-03. Cactus Data ApS, CPH.
' 2000-12-16. Leap year correction modified to be symmetrical.
'             Calculation of intDaysDiff simplified.
'             Renamed from YearsDiff() to Years().
' 2000-12-18. Introduced cbytMonthDaysMax.
' 2007-06-22. Version 2. Complete rewrite.
'             Check for month end of February performed with DateAdd()
'             after idea of Markus G. Fischer.

  Dim intDiff   As Integer
  Dim intSign   As Integer
  Dim intYears  As Integer
  
  ' Find difference in calendar years.
  intYears = DateDiff("yyyy", datDate1, datDate2)
  ' For positive resp. negative intervals, check if the second date
  ' falls before, on, or after the crossing date for a full 12 months period
  ' while at the same time correcting for February 29. of leap years.
  If DateDiff("d", datDate1, datDate2) > 0 Then
    intSign = Sgn(DateDiff("d", DateAdd("yyyy", intYears, datDate1), datDate2))
    intDiff = Abs(intSign < 0)
  Else
    intSign = Sgn(DateDiff("d", DateAdd("yyyy", -intYears, datDate2), datDate1))
    If intSign <> 0 Then
      ' Offset negative count of years to continuous sequence if requested.
      intDiff = Abs(booLinear)
    End If
    intDiff = intDiff - Abs(intSign < 0)
  End If
  
  ' Return count of years as count of full 12 months periods.
  Years = intYears - intDiff
  
End Function

Open in new window

/gustav

PS: Miriam, you know this.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39765006
Hi,

Here is a function that I use that's pretty clear:

Public Function AgeCalc(vDate1 As Date, vBirthDate As Date) As Long
    On Error Resume Next
    If Month(vDate1) < Month(vBirthDate) Or (Month(vDate1) = Month(vBirthDate) And Day(vDate1) < Day(vBirthDate)) Then
        AgeCalc = Year(vDate1) - Year(vBirthDate) - 1
    Else
        AgeCalc = Year(vDate1) - Year(vBirthDate)
    End If
End Function

Essentially if their birthday has past then math works - if not then subtract one year.

Usage:

Age=AgeCalc(DateToTest,Birthdate)

Regards,

Bill
0
 

Author Closing Comment

by:SteveL13
ID: 39765850
I had a typo which is why this wasn't working.  Once I fixed it... it worked!  Thanks.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39766034
So this was Excel? So why put it under Access?
0
 

Author Comment

by:SteveL13
ID: 39766184
It was Access.  But I used

"Date2 minus Date1
and use the INT function to change it to a whole number"

to get to the solution.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39766489
Are you serious? That's not a solution - except for sending birthday cards or so.

/gustav
0
 
LVL 23

Expert Comment

by:Eirman
ID: 39766980
Thanks for the points SteveL13

and my commiserations to all those who produced some great code/solutions without due reward.
0
 

Author Comment

by:SteveL13
ID: 39767931
It did solve the problem.  See attachment.
Clip1.jpg
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39768001
"Solve" is a strong word here. For example for this day, 2014-01-09, this method fails for those born on:

2013-01-09     1             0
2009-01-09     5             4
2005-01-09     9             8
2001-01-09     13            12
1997-01-09     17            16
1993-01-09     21            20
1989-01-09     25            24
1985-01-09     29            28
1981-01-09     33            32
1977-01-09     37            36
1973-01-09     41            40
1969-01-09     45            44
1965-01-09     49            48

Left coloumn is the correct age, right is the result from the expression.

Just a sample.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now