Calculating Age as of a certain date

[Swillsy]

Hi All,

I'm struggling with this expression in a text box on one of my forms and can't seem to find an easy answer via google.

=DateDiff("yyyy",[DOB],Date())

This works out the age of the person as of today
I need it to work out the age of the person as of the 01/09/2008

However as I dont want the customer to be changing the code each year is there anyway i can format DATE() so that it reads
01/09/(date(),yyyy) << completly wrong but you get the idea.

Cheers for your help.

 

[boblarson]

Code from RuralGuy:

Public Function Age(DOB As Date) As Integer

Age = DateDiff("yyyy", DOB, Date) + (Date < DateSerial(Year(Date), Month(DOB), Day(DOB)))

End Function

 

[Swillsy]

Cheers Bob\RuralGuy

Exactly what I was looking for

James

 

[raskew]

To compute age as of a certain month/day each year, you'll need something like this:

Public Function AgeAsOf(DOB As Date) As Integer
Dim dteKeep As Date

dteKeep = DateSerial(Year(Date), 1, 9)

AgeAsOf = DateDiff("yyyy", DOB, dteKeep) + (dteKeep < DateSerial(Year(Date), Month(DOB), day(DOB)))

End Function

Bob

 

[missinglinq]

raskew, I have to ask wjy you would come along 19 hours after Bob Larson has posted a working answer to this question and post the identical solution?

 

[raskew]

Bob Larson's solution returns age as of the date that the function is run. The OP asked for something

to work out the age of the person as of the 01/09/2008

.

What I posted, 19 hours later, was a modification of the original solution that returned age as of 1/9/year(date()), which was what the OP asked for.

Missinglinq, put your reading glasses on and you'll see the two functions are not identical.

Have a nice day - Bob

 

[Swillsy]

raskew is spot on i had to add the criteria to bobs original one.

Edited for a cheers to raskew

 

[missinglinq]

Sorry, but it's the identical code! Even the OP knew that you just substituted the date you wanted for comparison with Date()!

 

[raskew]

As I said

What I posted, 19 hours later, was a modification of the original solution

Wasn't as if I tried to hide the fact. My response was intended to answer the original problem since it wasn't clear whether the OP had the expertise to make the necessary changes.

So, what's the problem?

Bob

 

[boblarson]

It is true that it was slightly off as I didn't quite gather the requirements about the 1/9/2008 date.

Thanks Bob for posting the corrected version.

Thanks Linq for trying to stick up for me, but even I would have posted a correction to it if I had that revelation about the requested date.

And good going Swillsy for figuring out my mistake to begin with.

 

[raskew]

Hi there Mr./Ms. Missinglinq -

For someone who:

a) Provided no input to the OP's original request.

b) Failed to recognize that the original response, while accurate in itself, didn't fully address the problem presented.

c) Erroneously identified the alternate provided as identical to the original, and made it an issue worth posting.

d) Even after having it explained to you, continued to erroneously identify the alternate provided as identical to the original posted solution, and continued to make it a postable issue.

Gotta ask: Have you considered:

a) Getting a hobby?

b) Getting some mental health/anger management therapy (it's free or low-cost in most communities), or maybe studying-up for your GED?


Had to ask. Have a great day.

Bob

 

[palarm]

Amount of full years between dates

Function CalcAge(dteStart As Date, dteEnd As Date) As Long
Dim lngAge As Long
   lngAge = DateDiff("yyyy", dteStart, dteEnd)
   If DateSerial(Year(dteEnd), Month(dteStart), Day(dteStart)) > dteEnd Then
      lngAge = lngAge - 1
   End If
   CalcAge = lngAge
   If CalcAge < 0 Then CalcAge = 0
End Function

 

[raskew]

See post #2 -- it does it all in one line.

Bob