Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Calculating AGE from date field??

Posted on 2000-04-19
15
Medium Priority
?
441 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:tbigos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +4
15 Comments
 

Expert Comment

by:CAMELBUCK
ID: 2733167
tbigos,

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@onebox.com
0
 

Expert Comment

by:vanberge
ID: 2733434
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
0
 
LVL 2

Expert Comment

by:repstein
ID: 2733483
If you like one liners, just try:

age = format(DateDiff("d",BirthDate,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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Expert Comment

by:CAMELBUCK
ID: 2733641
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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2733689
An accurate oneliner:
Age = year(date() - BirthDate + 1)-1900
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2733695
Just a sample:
?year(#27-03-2006# - #28-03-1888# + 1)-1900
Gives 117

So it even works for the very old!
0
 
LVL 2

Expert Comment

by:repstein
ID: 2733867
to nico5038:

But what about those fractions...

Richard
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2734044
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
0
 
LVL 6

Expert Comment

by:billy21
ID: 2734138
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).
0
 
LVL 6

Accepted Solution

by:
billy21 earned 200 total points
ID: 2734145
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).

The fraction will only exist if "Age" is of the variable type: "Currency".  On screen the currency can be formatted as a fixed or General number displaying 2 decimal points (so the "$" doesn't show).
0
 
LVL 2

Expert Comment

by:repstein
ID: 2734154
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

0
 
LVL 54

Expert Comment

by:nico5038
ID: 2734362
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 !
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2734563
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

0
 
LVL 1

Author Comment

by:tbigos
ID: 2741026
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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2749633
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
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

610 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