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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

bccw

Thread Starter
Joined
Dec 4, 2001
Messages
3
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
Joined
Feb 14, 1999
Messages
11,749
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.
 
Joined
Oct 29, 2001
Messages
2,056
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

Thread Starter
Joined
Dec 4, 2001
Messages
3
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.
 
Joined
Oct 29, 2001
Messages
2,056
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)
 

Attachments

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
ROFL!

Sorry!!

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


(date calcs always calc the number of days, you wanted years)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top