calculate no. of years between a date and today's date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I get excel to work out how old someone is in years as at todays date
on any given day, when I enter a date of birth into a specific column on
excel 2003. I then need the formula to recognise if it is over 3 years of
age and irrelevant of how many years past 3, that it will put a one in the
formula cell column for that row. All the 1's in that column are
subsequently summed to give me a total of all people over 3 years of age.
Currently I am entering the date of birth and then manually calculate their
age and put the 1 into the column which then gets summed.
Any ideas?
 
A2: a date of birth

B2:

=(DATEDIF(A2,TODAY(),"Y")>=3)+0

Then:

=SUM(B2:B100)

Or:

=SUMPRODUCT((DATEDIF($A@:$A$100,TODAY(),"Y")>=3)+0)
 
Hi Aladin,
Thanks heaps. I have got it working in my other column now as well to
identify how many under 3 as well. Just brilliant! It's going to save heaps
of time.
Thanks ever so much.
 
Hi Aladin,
I have got a problem after all. The cells to be summed where the formula
is, defaults to "1" and so I don't get the correct no. of children over 3.
My empty rows where data is yet to be input at a future date is putting out
my total figure due to all the default "1"s. I need the cells to default to
"0" where there is no date of birth details. Apart from that it works great
where the date of birth is entered in a row.
Any ideas? Thanks
 
Use

=IF(A2="",0,(DATEDIF(A2,TODAY(),"Y")>=3)+0)

and

=SUMPRODUCT((A2:A100<>"")*(DATEDIF($A2:$A$100,TODAY(),"Y")>=3))
 
Hi Bob,
Your IF formula worked really well and has taken the column total back to
zero on all the rows where no date of birth is entered, so just great. I
also used your sum product formula but it ended up adding up all the zero's
and so I got 103 as a total while the sheet was blank. So I have just used
good old D2:D104 sum and it is working great. The formula Aladin gave me
works perfectly fine for my under 3 years old column, showing zero as the
default. Strange?? I copied and pasted the formula and just changed the >
to <. Any thoughts as to why.
 
B2, copied down:

=IF(A2="","",(DATEDIF(A2,TODAY(),"Y")>=3)+0)

to capture the situation that an A-cell does not house a birth date yet.

To secure the number of children older than 3 years:

=SUM(B2:B100)

To secure the number of children less than or equal to 3 years:

=COUNTIF(B2:B100,0)

Using a SumProduct formula, say in D2...

=SUMPRODUCT(ISNUMBER($A$2:$A$100)+0,(DATEDIF($A$2:$A$100,TODAY(),"Y")>3)+0)

This would give you the number of children over 3 years.

D3:

=COUNTA($A$2:$A$100)-D2

would then calculate the number of children, aged less than or equal to
3 years.

Qualification: Range A2:A100 should not house any text value, including
formula blanks (i.e., "").
 
Hi Su,

I get 0 in that case. 103 seems odd as we are only measuring 99 rows, so I
would expect 99 at max in any situation.
 
Hi Bob,
My row range is 104 including a header row so that is why I got 103.
However it is working great now thanks heaps.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating age from date of birth and ignoring if blank 5
Excel - Calculate age from DOB in years, months days 7
Excel Average dates help 0
Date Formula 85 factor 7
Completed years and monhts 6
How do I find age in years from anniversary date (Age funtion)? 1
How do I calculate a persn's age in years & months in Excel? 4
Function to take today's date less someone's b-day to get their ag 1

Back
Top