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

[Sue]

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?

[mangesh_yadav]

=DATEDIF(A1,B1,"y")

A1 = earlier date
B1 = later date

Mangesh

[Aladin Akyurek]

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)

Sue wrote:
> 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?

[Sue]

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.

"Aladin Akyurek" wrote:

> 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)
>
> Sue wrote:
> > 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?
>

[Sue]

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

"Aladin Akyurek" wrote:

> 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)
>
> Sue wrote:
> > 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?
>

[Bob Phillips]

Use

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

and

=SUMPRODUCT((A2:A100<>"")*(DATEDIF($A2:$A$100,TODAY(),"Y")>=3))


--
HTH

Bob Phillips

"Sue" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Aladin Akyurek" wrote:
>
> > 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)
> >
> > Sue wrote:
> > > 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?
> >

[Sue]

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.

"Bob Phillips" wrote:

> Use
>
> =IF(A2="",0,(DATEDIF(A2,TODAY(),"Y")>=3)+0)
>
> and
>
> =SUMPRODUCT((A2:A100<>"")*(DATEDIF($A2:$A$100,TODAY(),"Y")>=3))
>
>
> --
> HTH
>
> Bob Phillips
>
> "Sue" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Aladin Akyurek" wrote:
> >
> > > 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)
> > >
> > > Sue wrote:
> > > > 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?
> > >
>
>
>

[Aladin Akyurek]

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., "").

Sue wrote:
> 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
>
> "Aladin Akyurek" wrote:
>
>
>>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)
>>
>>Sue wrote:
>>
>>>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?
>>

[Bob Phillips]

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.

--
HTH

Bob Phillips

"Sue" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Bob Phillips" wrote:
>
> > Use
> >
> > =IF(A2="",0,(DATEDIF(A2,TODAY(),"Y")>=3)+0)
> >
> > and
> >
> > =SUMPRODUCT((A2:A100<>"")*(DATEDIF($A2:$A$100,TODAY(),"Y")>=3))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Sue" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > > "Aladin Akyurek" wrote:
> > >
> > > > 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)
> > > >
> > > > Sue wrote:
> > > > > 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?
> > > >
> >
> >
> >

[Sue]

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.

"Bob Phillips" wrote:

> 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.
>
> --
> HTH
>
> Bob Phillips
>
> "Sue" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> > "Bob Phillips" wrote:
> >
> > > Use
> > >
> > > =IF(A2="",0,(DATEDIF(A2,TODAY(),"Y")>=3)+0)
> > >
> > > and
> > >
> > > =SUMPRODUCT((A2:A100<>"")*(DATEDIF($A2:$A$100,TODAY(),"Y")>=3))
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Sue" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > > "Aladin Akyurek" wrote:
> > > >
> > > > > 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)
> > > > >
> > > > > Sue wrote:
> > > > > > 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?
> > > > >
> > >
> > >
> > >
>
>
>

[Sue]

Hi
Thanks heaps. It is working really well now. Really appreciate it.

"Aladin Akyurek" wrote:

> 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., "").
>
> Sue wrote:
> > 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
> >
> > "Aladin Akyurek" wrote:
> >
> >
> >>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)
> >>
> >>Sue wrote:
> >>
> >>>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?
> >>
>