HOW TO CALCULATE AGES IN AN ENTIRE COLUMN??

  • Thread starter Thread starter Meadow
  • Start date Start date
M

Meadow

Hullo all - in Excel 2007, I have one column with dob's and another with
test-dates - how to fill a 3rd column with people's ages as at their test
dates? "YEARFRAC" does it for ONE cell at a time, but WONT accept e.g.
=Yearfrac(e2:e741, s2:s741) to do the calculation for the ENTIRE column.

I tried subtracting one column from another by highlighting all of the new
column, entering "=s2:s741 - e2:e741" and then pressing ctrl-shift-enter, BUT
(a) it shows whole days (and I cant see a formula to convert whole days to
years for the ENTIRE column) and (b) it creates an "Array" and then I cant
seem to delete any bad values from the data (where there is a dob missing).

As a first-time user of Excel, I must say it's VERY obscure and
counter-intuitive etc - I started using Word2007 immediatley with no problem,
all seems self-explanatory!

ANy help appreciated! Thankyou!
 
You would usually use a formula specific to the first row, i.e. in row 2 use
the formula

=YEARFRAC(E2,S2)

then copy this down the column. To do that you place the cursor on the
bottom right corner of the cell until you see a black +, this is the "fill
handle". Left-click, hold down and drag as far as needed.........OR....if you
have continuous data in an adjacent column, e.g. if you're imputting formula
in T2, you can just double-click fill handle and formula will populate as far
down as you have data.

Note: if you just want the age in whole years try DATEDIF

=DATEDIF(E2,S2,"y")
 
it worked! thanks heaps!

daddylonglegs said:
You would usually use a formula specific to the first row, i.e. in row 2 use
the formula

=YEARFRAC(E2,S2)

then copy this down the column. To do that you place the cursor on the
bottom right corner of the cell until you see a black +, this is the "fill
handle". Left-click, hold down and drag as far as needed.........OR....if you
have continuous data in an adjacent column, e.g. if you're imputting formula
in T2, you can just double-click fill handle and formula will populate as far
down as you have data.

Note: if you just want the age in whole years try DATEDIF

=DATEDIF(E2,S2,"y")
Click to expand...
 
Back
Top