finding someone's age

Greetings,

If someone's date of birth is March 5, 1982, and I need to find out how old they were as of February 28, 2006, what fucntion could I use.

When I use

client_age:datediff("yyyy", [DOB], "2/28/06") ,

the age result is a year bigger than it should be.

Tried a couple other differnt ways; but it doesn't like the syntax. Please help!

Thanks!

Jim

If all you are interested in the number of years ("I am 29...again") then try:

VB Code:

'<CSCM>
'---------------------------------------------------------------------
'-- Name:   GetPersonsAge
'--   By:   <SGD> on 04/27/2006
'-- Desc:   Returns the exact age of a person in years based on birthday, and
'--     an optional second date (default value is NOW)
'---------------------------------------------------------------------
'-- Note:   DateDiff does not work properly for this.
'---------------------------------------------------------------------
'-- Revd:   <SGD> - 05/26/2006
'---------------------------------------------------------------------
'-- Parm:   BirthDate (Variant)
'           LaterDate (Variant)
'---------------------------------------------------------------------
'</CSCM>
Public Function GetPersonsAge(BirthDate As Variant, _
                              Optional LaterDate As Variant) As Long
    
    Dim dteLaterDate As Date
    If IsDate(BirthDate) Then
        If Not (IsDate(LaterDate)) Then
            dteLaterDate = CVDate(Now)
        Else
            dteLaterDate = LaterDate
        End If
        '---------------------------------------------------------------------
        GetPersonsAge = Year(dteLaterDate) - Year(BirthDate)
        '---------------------------------------------------------------------
        '-- Subtract one if this year's birthday hasn't occurred yet
        '---------------------------------------------------------------------
        If DateSerial(Year(dteLaterDate), Month(BirthDate), Day(BirthDate)) > dteLaterDate Then
            GetPersonsAge = GetPersonsAge - 1
        End If
    End If
End Function
'*********************************************************************
'-- EXAMPLE CODE
'*********************************************************************
'-- Assume that today is 5/8/2006
'Debug.Print GetPersonsAge("05/31/1950")                    '-- Prints 55 which is correct
'Debug.Print DateDiff("yyyy", "05/31/1950", "05/08/2006")   '-- Prints 56 which is incorrect
'Debug.Print GetPersonsAge("05/32/1950")                    '-- Prints 0 which is correct

Quote:

---

Originally Posted by JimMuglia

Greetings,

If someone's date of birth is March 5, 1982, and I need to find out how old they were as of February 28, 2006, what fucntion could I use.

When I use

client_age:datediff("yyyy", [DOB], "2/28/06") ,

the age result is a year bigger than it should be.

Tried a couple other differnt ways; but it doesn't like the syntax. Please help!

Thanks!

Jim

---

are you sure??

i tried this:

VB Code:

DateDiff("yyyy", "3/5/1982", "2/28/2006")

and it worked fine. showed 24

?? datediff("yyyy", #3/5/1982#, #2/28/2006#) = 24 which is correct

change your 2/28 to a date... and try cdate([dob])

VB Code:

datediff("yyyy", CDate([DOB]), #2/28/2006#)

...I must be missing something: If I was born in 1982 but my birthday in 2006 has NOT come yet, then I am 23, not 24....??? :D

if your birthday has NOT come up yet.. true.. BUT
if u are comparing it to 2/28/2006 then yes.. u are 24

Quote:

---

Originally Posted by sigid

...I must be missing something: If I was born in 1982 but my birthday in 2006 has NOT come yet, then I am 23, not 24....??? :D

---

in that case, try sigid example.

oops.. its rounding up!!

d = DateDiff("d", #8/22/1986#, Date)
MsgBox d / 365
19.8

DateDiff("yyyy", #8/22/1986#, Date)
20

Static -
- I agree with everything you said....
If I was born on March 5, 1982 and today is 2/28/2006 (not yet March 5, 2006) then...23...

heres a one liner! LOL

VB Code:

Left(CStr(DateDiff("d", #8/22/1986#, Date) / 365), InStr(CStr(DateDiff("d", #8/22/1986#, Date) / 365), ".") - 1)

this one is much better:

VB Code:

Dim theage As String
theage = InputBox("How old are you?")

lol...:D:lol::D

Are you with me folks! :D

http://home.rochester.rr.com/lgsstatic/smiles/lach.gif

Try this one out for size:
? Left(CStr(DateDiff("d", #6/9/1986#, Date) / 365), InStr(CStr(DateDiff("d", #6/9/1986#, Date) / 365), ".") - 1)

Since today is 6/8/06, and the above yields 20, instead of 19... :)
(also yields 20 for 6/10/1986, 6/11/1986...why am I such a nitpicker???)

oops.. leap years..
Left(CStr(DateDiff("d", #6/9/1986#, Date) / 365.25), InStr(CStr(DateDiff("d", #6/9/1986#, Date) / 365.25), ".") - 1)

Blah.. ok.. so it wasnt perfect.. If u put todays date it to breaks :( oh well.. gave it a shot LOL

Wait!! one more shot.. I think I got it!

One line :)
oh.. taking input from text1 ....

VB Code:

Left(CStr(DateDiff("d", CDate(Text1), Date) / 365.25), _
            IIf(InStr(CStr(DateDiff("d", CDate(Text1), Date) / 365.25), ".") <> 0, _
            InStr(CStr(DateDiff("d", CDate(Text1), Date) / 365.25), ".") - 1, _
            Len(CStr(DateDiff("d", CDate(Text1), Date) / 365.25))))

http://www.vbforums.com/

Sorry, but when you hardcode 365.25, the starting date becomes critical.

Try the above for 6/8/1988 and you'll get 17...

(and some time real soon now, I am definitely going to get a real life...)

http://www.vbforums.com/
bah...

17.9986310746064
so close! thats it.. I give up...

(Don't forget the International Date Line)

i haven't tested it, but try this on for size:

VB Code:

Dim lAge As Long
    lAge = DateDiff("yyyy", #8/22/1986#, Now)
    If CDate(Format$(Now, "mm/dd")) < CDate(Format$(#8/22/1986#, "mm/dd")) Then lAge = lAge - 1
    Debug.Print lAge

I should have posted this in the database section, as I need to put it for a value in the Access query wizard. I tried the left command above, and its giving me an "invalid procedure call".

Jim

OOOHH in access.. ok
use Sigid's code (towards the top)

drop the function into a module then use it in the field

AGE:GetPersonsAge([DOB],#2/28/2006#)

should work fine

here's mine in functional form:

VB Code:

Private Function GetAge(ByVal dDOB As Date, ByVal dDate As sdate) As Long
    GetAge = DateDiff("yyyy", dDOB, dDate)
    If CDate(Format$(dDate, "mm/dd")) < CDate(Format$(dDOB, "mm/dd")) Then GetAge = GetAge - 1
End Function

that'll work fine in VBA (i think)

yes it will

although be forewarned.. it will slow down your query to call functions like that

how bout this?

VB Code:

Dim dtBirth As Date, dtNow As Date, dtVar As Date
Dim lngAge As Long
 
dtNow = Date
dtBirth = "6/10/2000"
lngAge = DateDiff("yyyy", dtBirth, dtNow)
dtVar = DateAdd("YYYY", lngAge, dtBirth)
If dtVar > dtNow Then
    lngAge = lngAge - 1
End If
MsgBox lngAge

I know this thread is a couple of days old now, but i thought of a better way to do it:

VB Code:

Private Function GetAge(ByVal dDOB As Date) As Long
    GetAge = Year(Now - dDOB + 2) - 1900
End Function

On the person's birthday it should advance a year. IOW, if you were born on 6/12/2000, you're 6 today. So the algorithm should be GetAge = Year(Now - dDOB + 1) - 1900, or it'll be off by one day. (Unless age is figured differently in the UK?)

i didn't really test it - you're probably right. code above ammended.

Edit: I think it needs to be +2 actually.

Wow - I cannot believe this thread...

Calc'ing AGE is a standard formula - not really open for debate!

But not on this forum ;)

[edit] I thought this would blow up in VB with Feb 29...

dtVar = DateAdd("YYYY", lngAge, dtBirth)

but it only blows up with DATEADD in T-SQL

Quote:

---

Originally Posted by bushmobile

i didn't really test it - you're probably right. code above ammended.

Edit: I think it needs to be +2 actually.

---

For a birthday of tomorrow (mine, actually), 1 makes me a year older from tomorrow on. 2 makes me a year older today.

well for the example you gave it needs to be +2. ahh well, s*d it!

Chekout this thread also... ;)

http://www.vbforums.com/showthread.p...highlight=year

Doing division and math tricks to determine an age is a dangerous path to take. It might look neat - but it's simply not a proper formula for age.

Calculating an age has to be one of the simplest logic-flow concepts to understand. It has one PROCESS step and one CONDITION step...

1) PROCESS: Subtract Birth year from Current Year
2) CONDITION: Is Current MM/DD greater than Birth MM/DD
3) NO: Subtract 1
4) YES: Do nothing

Result...Age

You can do this without using DATEDIFF and DATEADD - it's the easiest of math.

It works in VB - it works in SQL - it works in any language.

It handles leap years - not through any effort, but simply because the logic flow mimics the real life calculation that one would do in their own head to determine their own age.

No one is dividing by 365.25 in their head ;)

no-one is saying that doesn't work, szlamany - it was the very first reply. i think people are just trying to think of different ways.

My concern is that those other techniques are ridden with possibilies for bugs - division, rounding, variable datatypes (float/integer).

I'll keep quiet now :)

But how the get axact today age in year Month Day format.

Quote:

---

Originally Posted by shakti5385

But how the get axact today age in year Month Day format.

---

what do you mean?


and if anyone's bothered to read down this far, here's the age calculation without an if:

Code:

---

Private Function GetAge(ByVal DOB As Date) As Long
    GetAge = DateDiff("yyyy", DOB, Date) + (Date < DateSerial(Year(Date), Month(DOB), Day(DOB)))
End Function

---

I just found this (again!) and I like bushmobile's formula better than my own...:blush: