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

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

#### 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

Anne
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
 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)

#### Attachments

• 24.4 KB Views: 100

#### Anne Troy

Anne
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

Anne
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!!!

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

As Seen On