(solved) Excel: How to Calculate Age w/No Decimals

[bccw]

How do I calculate age from a date of birth in excel? The output must be in the form of a whole number from which I can calculate mean age etc..

 

[Anne Troy]

Date of birth in cell A1


=now()-a1 into the cell where you want the age to appear. This cell must be formatted as a number with 0 decimals, and NOT as a date.

 

[jbcalg]

i had a more complicated version, using today, year functions, not a one stepper by any means (4 steps really)

just a comment: given the usual time lag with demographic data, you might want to consider using a constant for your current year instead of a now or today function depending on the time period your stats are covering.

ie
mean age in 2000 using 2000 data would be wrong if you used now/today functions in your calculations, any more wrong in another month when your PC's date switches to 2002.

 

[bccw]

Thank you 'Dreamboat' but unfortunately I couldn't get that to work (I've never encountered people that old!). Jbcalg - details of your four steps would be appreciated.

 

[jbcalg]

i assumed you were working with a mm/dd/yy date format

first convert to mm/dd/yy to year
find today's date (see comments in previous post)
convert to 4 digit year
subtract

here's the orig:
here's a complicated way LOL

dob in col A
convert dob to number in col B using YEAR function
today's date function in col C using TODAY
convert current date to year in col D using YEAR
[edit] subtract B from D in col E

you can do calculations with col E but it's formula, not a number/integer,
so you might want to - copy/paste special, value - col E to get a value to use in your calculations

there's likely easier ways to do it, and i'd probably throw all this into a lookup type sheet, then just have the final age integer to work with on a main cal sheet

here's a pic of the calcs and the formulas - formulas apply to row 3, copy down for subsequent rows (could probably use a $ ref for the converted current year)

 

[Anne Troy]

ROFL!

Sorry!!

That's =(now()-a1)/365.25


(date calcs always calc the number of days, you wanted years)

 

[jbcalg]

365.25 (approx) actually in the julian calendar

 

[Anne Troy]

Isn't that what I put?





(LOL, thanks JB, you're perfectly right)

 

[bccw]

Great, sorted. Many thanks both.

 

[jbcalg]

Originally posted by Dreamboat
Isn't that what I put?





(LOL, thanks JB, you're perfectly right)

bet you mourned the passing of invisible ink!!!