|   Home    |    Excel    |    Access   |   Word   |   Outlook   |   FrontPage   |   VBA   |   Downloads   |   Index   |
Calculate Age...

 

Working out Someone's Age

When storing personal data, it is normal practice to record a person's date of birth rather than their age. This is for the simple reason that the date of birth never changes, but the age will be different next year. If you need to show someone's age you should ask Access to calculate it for you.

Calculating someone's age from their birthday is not quite as simple as it might at first seem! Of course, you take the current year and subtract it from the year in which they were born. This will give you an accurate age in years - providing they have had their birthday this year. So, to be absolutely certain you need to decide if the day and month of their birthday has passed yet.

If it has you subtract their birth year from the current year. If not, then you subtract their birth year from the current year, and then subtract 1 from the answer. A simple calculation we do in our heads without even thinking about it, but quite a task to explain to Access. To get Access to do it this way requires a number of conditional statements, resulting in a very complicated formula! (Conditional statements are easier in Excel that in Access. Take a look at the example in the tutorial Working Out a Person's Age in Excel).

Fortunately, there are a couple of easier ways. Providing you understand the results they provide and use them accordingly, they should suit most purposes. Both examples below take birth dates from a field called DoB and calculate an age, which is displayed in a new field called Age.


To Calculate Approximate Age

Use this expression...   Age: Year(Now( ))-Year([DoB])

Calculating the approximate age

 

Calculating the approximate age - result

Age: creates a new field called Age
Year(Now( )) calculates the year number from the current year
Year([DoB]) calculates the year number from the date it finds in the [DoB] field

The expression subtracts the person's birth year from the current year to give an approximate age, but does not take account of whether or not thy have had their birthday. It will tell you how old they will be this year.

 


To Calculate Accurate Age

Use this expression...   Age: (Date()-([DoB])/365.25

 It makes use of the fact that Access uses serial numbers to store dates

 
Calculating age accurately

 

Calculating age accurately - result

Date( ) calculates today's date

The expression subtracts the person's birth date serial number (which it finds in the field [DoB]) from today's serial number, leaving the number of days in between. It then divides that number by 365.25 (being the average number of days in a year) to give their age in years.

In this example the field has been formatted to show two decimal places.


Applying the Technique

You can use these expressions to calculate someone's age on a particular date, and refine the query by adding criteria to display only the records for people whose ages fall within a particular range

Calculating age on a specific date
Calculating age on a specific date - result This example creates a new field called Age and calculates the person's age on a particular date, which it finds in a field called Chosen Date. It then uses the expression <35 in the criteria row to display only the results that calculate to less than 35.

The result is a list of people who were under 35 years of age on the date in question.

If you want to calculate people's ages on a particular date, providing that it is the same date for everyone, you don't need to retrieve that date from a field. Simply include it in the expressionů

Age: (#01/11/65#-[DoB])/365.25 would create a new field called Age and calculate how old the people were on 1st November 1965. Note: If the person's birth date is later than the date used in the expression (i.e. they had not been born by that date) their age would show as a minus number!

^ top
   

 

 

 

 

Hit Counter