The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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?

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?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

and use the INT function to change it to a whole number

Various methods are described here ...

http://www.fontstuff.com/excel/exltut01.htm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialWith Age = Abs(datediff("yyyy",Date2,

With the other suggestion I get 115

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

Debug.Print Abs(datediff("yyyy",#1/1/2

Ages.xls

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

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?

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

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

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.

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

/gustavPS: Miriam, you know this.

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,Bir

Regards,

Bill

"Date2 minus Date1

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

to get to the solution.

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

It did solve the problem. See attachment.

Clip1.jpg

Clip1.jpg

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.

Microsoft Access

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

Open in new window