Calculating Age from Birthdate

[ATC]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a spreadsheet with a lot of names and birthdates and would like to find a formula which calculate their age to the nearest year or half year e.g 30 or 30.5, based on the current date.

Sorry if this is a really dumb/simple question. Just couldn't find anything in the usual "help" tools.

Thanks

 

[CyberTaz]

If the birth date is in A1 create a formula in A2 as follows:

=(TODAY()-A1)/365

Format A2 for either 1 or 0 decimal display.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[ATC]

Thank you so much!

 

[ATC]

Thanks but I also want to round the age down to the nearest half year e.g 30yrs to 30yrs 5months = 30yrs and.....30yrs 6 months to 30 yrs 9 months = 30.5yrs etc. Simply using the 1 decimal display rounds to one tenth (as opposed to one twelfth of a calendar year)and only up.

Tried fiddling with the RoundDown and MRound formulas but couldn't quite get it to work.

 

[John McGhie]

Yeah, I had to look this one up myself!

Assuming a birthday in cell D7, the one you want is
=INT(D7/365.25)+MROUND((MOD(D7,365.25)/365.25),0.5)

First, use the INT function to return a whole number of years.

Then the MOD function to give you the remainder, express the remainder as a
fraction of a year, then MROUND that to the nearest 0.5.

Hope this helps

Thanks but I also want to round the age down to the nearest half year e.g
30yrs to 30yrs 5months = 30yrs and.....30yrs 6 months to 30 yrs 9 months =
30.5yrs etc. Simply using the 1 decimal display rounds to one tenth (as
opposed to one twelfth of a calendar year)and only up.

Tried fiddling with the RoundDown and MRound formulas but couldn't quite get
it to work.

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]