tbigos
asked on
Calculating AGE from date field??
Hi,
I need to calculate age as a number from another field that is a date filed using the short date form.
eg. Birthdate 04/28/1970
I need to calcualte date as of todays date. Also is it possible to calcualte the age with fractions?
ie. 8.5 for 81/2 years
thanks
I need to calculate age as a number from another field that is a date filed using the short date form.
eg. Birthdate 04/28/1970
I need to calcualte date as of todays date. Also is it possible to calcualte the age with fractions?
ie. 8.5 for 81/2 years
thanks
Hey tbigos,
Here is a quick and simple function for you to try. It will take the value of a date (I used a random date 06/28/1975) and show you in decimal value (24.83) the number of years you have been alive. I did not put in any error checking, but this as long as there is a date in myBDate, everything will be fine. Then it shows a msgbox with the value.
I hope this helps you, let me know if it does not work properly for you.
Eric
Function cmdFigureOutDate()
'To use this function as part of AfterUpdate, or OnChange for
'your fields. Then set myBDate to the value on your form, for example
'myBDate=me.txtBDay.value
'I have a msgBox showing what the number value is, however you can use
'myDecNum to set the value on your form.
'Varibles are as follows
'myBDate is the date of the birthday
'myBNumDays is the difference of days between now and the birthday
'myDecNum is the numerical equivalant to years alive
'myDecNum is a double to show the decimal value
'myMsg is the text for the msgBox
Dim myBDate As Date
Dim myBNumDays As Integer
Dim myDecNum As Double
Dim mymsg As String
myBDate = #6/28/75# 'Remeber to set a variable to a date use # to enclose the date
myBNumDays = Abs(DateDiff("d", Now, myBDate))
myDecNum = Format((myBNumDays / 365), "###0.00")
mymsg = "You have been alive " & myDecNum & " Years!"
MsgBox mymsg
End Function
Here is a quick and simple function for you to try. It will take the value of a date (I used a random date 06/28/1975) and show you in decimal value (24.83) the number of years you have been alive. I did not put in any error checking, but this as long as there is a date in myBDate, everything will be fine. Then it shows a msgbox with the value.
I hope this helps you, let me know if it does not work properly for you.
Eric
Function cmdFigureOutDate()
'To use this function as part of AfterUpdate, or OnChange for
'your fields. Then set myBDate to the value on your form, for example
'myBDate=me.txtBDay.value
'I have a msgBox showing what the number value is, however you can use
'myDecNum to set the value on your form.
'Varibles are as follows
'myBDate is the date of the birthday
'myBNumDays is the difference of days between now and the birthday
'myDecNum is the numerical equivalant to years alive
'myDecNum is a double to show the decimal value
'myMsg is the text for the msgBox
Dim myBDate As Date
Dim myBNumDays As Integer
Dim myDecNum As Double
Dim mymsg As String
myBDate = #6/28/75# 'Remeber to set a variable to a date use # to enclose the date
myBNumDays = Abs(DateDiff("d", Now, myBDate))
myDecNum = Format((myBNumDays / 365), "###0.00")
mymsg = "You have been alive " & myDecNum & " Years!"
MsgBox mymsg
End Function
If you like one liners, just try:
age = format(DateDiff("d",BirthD ate,Date)/ 365,"#.0")
(This doesn't correct for leap years and leap centuries, but it's close enough for government work)
A similar function is buried in the middle of vangerge's function, but this is much more compact.
Richard
age = format(DateDiff("d",BirthD
(This doesn't correct for leap years and leap centuries, but it's close enough for government work)
A similar function is buried in the middle of vangerge's function, but this is much more compact.
Richard
Richard,
You crack me up. I totally agree... it's close enough for government work. LOL
I should know I spent one year with the government... don't know if I will ever again.
Take it easy all.
Jesse
You crack me up. I totally agree... it's close enough for government work. LOL
I should know I spent one year with the government... don't know if I will ever again.
Take it easy all.
Jesse
An accurate oneliner:
Age = year(date() - BirthDate + 1)-1900
Age = year(date() - BirthDate + 1)-1900
Just a sample:
?year(#27-03-2006# - #28-03-1888# + 1)-1900
Gives 117
So it even works for the very old!
?year(#27-03-2006# - #28-03-1888# + 1)-1900
Gives 117
So it even works for the very old!
to nico5038:
But what about those fractions...
Richard
But what about those fractions...
Richard
Do you give your age with fractions?
But if you want them:
AGE = (date() - BirthDate + 1) / 365.25
Think this is the fastest to get a rather accurate result. (Leapyears are at least counted correctly for every four years somebody lives)
Sample for a 50 year person:
?(date() - #28-03-1950# + 1)/365
50,1013698630137
?(date() - #28-03-1950# + 1)/365.25
50,0670773442847
But if you want them:
AGE = (date() - BirthDate + 1) / 365.25
Think this is the fastest to get a rather accurate result. (Leapyears are at least counted correctly for every four years somebody lives)
Sample for a 50 year person:
?(date() - #28-03-1950# + 1)/365
50,1013698630137
?(date() - #28-03-1950# + 1)/365.25
50,0670773442847
Age = datediff("d",DOB,Now)
Age = Age / 365.25
This two simple lines of code will give you a persons age in years and a fraction (ie 18.25).
Age = Age / 365.25
This two simple lines of code will give you a persons age in years and a fraction (ie 18.25).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
But:
1. Your initial code doesn't work for age<1 (reports age as 0)
2. 365.25 dividend only is correct every 4 years, since the extra 0.25 day is not added yearly. And leap centuries occur every 400 years. What's a day between friends?
3. The questioner asked about fractions in ages, not me. I usually give my age in hex or BCD
4. I don't think your code will handle the Y10k problem :-)
Richard
1. Your initial code doesn't work for age<1 (reports age as 0)
2. 365.25 dividend only is correct every 4 years, since the extra 0.25 day is not added yearly. And leap centuries occur every 400 years. What's a day between friends?
3. The questioner asked about fractions in ages, not me. I usually give my age in hex or BCD
4. I don't think your code will handle the Y10k problem :-)
Richard
Richard,
About Y10K, an old dutch saying:
Who then lives who then cares!
As a matter of fact, depending on the internal size the relative daynumber is stored, I assume even Access could fail long before 10.000 !
About Y10K, an old dutch saying:
Who then lives who then cares!
As a matter of fact, depending on the internal size the relative daynumber is stored, I assume even Access could fail long before 10.000 !
Hi.
Maybe there is a reason to calculate DateDiff in monthes, i.e.
Age = Round(DateDiff("m", BerthDate, Date())/12, 2)
This works for age more then 1 month; Round works in VBA 6.0 (Acc2000).
Regrds,
Dedushka
Maybe there is a reason to calculate DateDiff in monthes, i.e.
Age = Round(DateDiff("m", BerthDate, Date())/12, 2)
This works for age more then 1 month; Round works in VBA 6.0 (Acc2000).
Regrds,
Dedushka
ASKER
Thanks to ALL that posted comments.
I'm really lazy so I always look for the shortest and most simple solution.
one, two liners are the best..thanks again.
I'm really lazy so I always look for the shortest and most simple solution.
one, two liners are the best..thanks again.
Your choice beats me tbigos as:
AGE = (date() - BirthDate + 1) / 365.25
Is a one liner even faster as the two liner, because no function is used, just the numerical value (number of days since 1899-12-30) that the date stands for!
Nico
AGE = (date() - BirthDate + 1) / 365.25
Is a one liner even faster as the two liner, because no function is used, just the numerical value (number of days since 1899-12-30) that the date stands for!
Nico
Hi. This is a common, yet slightly complex question. I am researching this for you now. What I have come up with is some VBA coding in the form of a Function. It may help. Of course there are many other ways to do this as well. Hopefully this is a start.
Function dhAge(dtmBD As Date, Optional dtmDate As Date = 0) As Integer
Dim intAge As Integer
If dtmDate = 0 Then
dtmDate = Date
End If
intAge = DateDiff("yyy", dtmBD, dtmDate)
If dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), Day(dtmBD)) Then
intAge = intAge - 1
End If
dhAge = intAge
End Function
Jesse
godlendreamsproductions@on