Age Calculation
in [General]
|
Prev: cpu
Next: Min Max dates not consistent
From: zyus on 13 May 2010 20:41 I have the following sample field and record Age ApprovedDate 40 15-05-2000 Age-number field that shows the current age in 2010 Approveddate - date field when the application was approved. How to calculate the age of the applicants at the point of approval. In the above example the age would be 30... Thanks
From: John W. Vinson on 13 May 2010 21:36 On Thu, 13 May 2010 17:41:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >I have the following sample field and record > >Age ApprovedDate >40 15-05-2000 > >Age-number field that shows the current age in 2010 >Approveddate - date field when the application was approved. > >How to calculate the age of the applicants at the point of approval. In the >above example the age would be 30... > >Thanks > The Age field should SIMPLY NOT EXIST in your table. If it's right today, it will be wrong for every person in your database in 12 months. I am 63 years old in 2010; I'll also be 64 years old in 2010 (in another week in fact). How was this field derived? What does it MEAN - was the person 40 on January 1, or on December 31, or what? If the person's birthday was 14-05-1970 you're "age at point of approval" would be 30, if their birthday were the 16th it would be 29; which answer do you want? -- John W. Vinson [MVP]
From: zyus on 14 May 2010 00:11 Thanks for your reply, The data is imported from text files that extracted from other system and calculated on Jan 1 basis. The data is for analysis purpose and will be updated/replaced every month. "John W. Vinson" wrote: > On Thu, 13 May 2010 17:41:01 -0700, zyus <zyus(a)discussions.microsoft.com> > wrote: > > >I have the following sample field and record > > > >Age ApprovedDate > >40 15-05-2000 > > > >Age-number field that shows the current age in 2010 > >Approveddate - date field when the application was approved. > > > >How to calculate the age of the applicants at the point of approval. In the > >above example the age would be 30... > > > >Thanks > > > > The Age field should SIMPLY NOT EXIST in your table. If it's right today, it > will be wrong for every person in your database in 12 months. I am 63 years > old in 2010; I'll also be 64 years old in 2010 (in another week in fact). > > How was this field derived? What does it MEAN - was the person 40 on January > 1, or on December 31, or what? If the person's birthday was 14-05-1970 you're > "age at point of approval" would be 30, if their birthday were the 16th it > would be 29; which answer do you want? > > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 14 May 2010 01:17 On Thu, 13 May 2010 21:11:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >Thanks for your reply, > >The data is imported from text files that extracted from other system and >calculated on Jan 1 basis. > >The data is for analysis purpose and will be updated/replaced every month. > > Please answer my question: >What does it MEAN - was the person 40 on January > 1, or on December 31, or what? If the person's birthday was 14-05-1970 you're > "age at point of approval" would be 30, if their birthday were the 16th it > would be 29; which answer do you want? > Having an AGE field in your table is HOPELESSLY AMIGUOUS. It makes it impossible, even in principle, to get an exact age as of the point of approval. The information to provide that answer simply does not exist. So you'll have to settle for an approximation; I'm asking you - which approximation do you want? Try Year(Date()) - Year([DateOfApproval]) + [Age] to get an approximation. It'll be wrong half the time by one year, but you can't do better than that with the information you've posted. -- John W. Vinson [MVP]
From: John W. Vinson on 14 May 2010 11:45
On Thu, 13 May 2010 21:11:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >The data is imported from text files that extracted from other system and >calculated on Jan 1 basis. > I was 63 on January 1. How old am I today? Am I 63 or 64? Might be either, depending on my birthday. Do you get my point? Your question, as posted and with the data that you have available, *cannot be answered*. -- John W. Vinson [MVP] |