Query to calculate a persons age in Years & Months

J

Jeepster

New Member
Joined
Jun 9, 2005
Messages
23
Hi there,

I want to run a report that will calculate an employee’s age if I enter a date in a prompt box. My efforts so far produce the person’s age as a decimal. For instance, if an employee is 40 years and three months old (today), the query returns a figure of 40.25. How do I write a query that will show the person’s age in years and months (rounded up or down)?

Thank you
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Hi
Assuming you have a field in your table called 'DOB' (be sure to modify according to your actual field name) then the age in years up to today is calculated in a query using the following expression :

Code: 
Years : Year(Date()) - Year([DOB]) - IIf(Month(Date()) < Month([DOB]), 1, IIf(Month(Date()) > Month([DOB]), 0, IIf(Day(Date())<Day([DOB]), 1, 0)))

And the months can be calculated in the same query using this expression :

Months : Month(Date()) - Month([DOB]) + IIf(Month(Date()) < Month([DOB]), 12, IIf(Month(Date()) = Month([DOB]) And Day(Date()) < Day([DOB]), 12, 0)) - IIf(Day(Date()) < Day([DOB]), 1, 0)

If you want to choose a date rather than compare the birthday to today, then use something like the following instead :

Code: 
Years : Year([Enter Date])-Year([DOB]) - IIf(Month([Enter Date]) < Month([DOB]), 1, IIf(Month([Enter Date]) > Month([DOB]), 0, IIf(Day([Enter Date]) < Day([DOB]), 1, 0)))

Months : Month([Enter Date]) - Month([DOB]) + IIf(Month([Enter Date]) < Month([DOB]), 12, IIf(Month([Enter Date]) = Month([DOB]) And Day([Enter Date]) < Day([DOB]), 12, 0)) - IIf(Day([Enter Date]) < Day([DOB]), 1, 0)

The 2 fields can be joined into 1 field using the ampersand symbol with some basic text formatting, e.g.

Code: 
Year([Enter Date]) - .... <Day([DOB]),1,0))) & " Years, " & Month([Enter Date]) .... Day([DOB]),1,0) & " Months".

The 4 dots signify the missing parts of the code which would need to be included. Remember to use your actual field name.

HTH, Andrew :)
 
Upvote 0
Thanks..

Andrew

This is exactly what I was looking for. Your help and quick response is greatly appreciated.

Many thanks


Alan
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Age in Years/Months
Replies
1
Views
298
duesouth
D
J
  • Solved
Determine total numbers of days worked in a given month/year from a table of date ranges
Replies
3
Views
565
Eric W
Eric W
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
418
STARL0RD
S
J
  • Question Question
Formula to calculate tenure (Years & Months)
2
Replies
12
Views
7K
Alex Blakenburg
A
P
  • Question Question
calculate age of a person
Replies
6
Views
1K
flyingtony
F

Forum statistics

Threads
1,224,548
Messages
6,179,451
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top