Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sue
 
Posts: n/a
Default calculate no. of years between a date and today's date

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?
  #2   Report Post  
mangesh_yadav
 
Posts: n/a
Default


=DATEDIF(A1,B1,"y")

A1 = earlier date
B1 = later date

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378377

  #3   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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?

  #4   Report Post  
Sue
 
Posts: n/a
Default

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?


  #5   Report Post  
Sue
 
Posts: n/a
Default

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?




  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

Use

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

and

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


--
HTH

Bob Phillips

"Sue" wrote in message
...
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?




  #7   Report Post  
Sue
 
Posts: n/a
Default

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,TODA Y(),"Y")=3))


--
HTH

Bob Phillips

"Sue" wrote in message
...
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?




  #8   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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?


  #9   Report Post  
Bob Phillips
 
Posts: n/a
Default

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" wrote in message
...
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,TODA Y(),"Y")=3))


--
HTH

Bob Phillips

"Sue" wrote in message
...
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?






  #10   Report Post  
Sue
 
Posts: n/a
Default

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" wrote in message
...
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,TODA Y(),"Y")=3))


--
HTH

Bob Phillips

"Sue" wrote in message
...
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?









  #11   Report Post  
Sue
 
Posts: n/a
Default

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?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically enter today's date as a static entry David Excel Worksheet Functions 1 June 4th 05 04:54 PM
today's date CK Excel Worksheet Functions 2 May 18th 05 07:19 PM
Calculate Years of Service Jerry Excel Worksheet Functions 5 February 24th 05 01:27 AM
Calculat years married or age of someone on today's date Chersie Excel Worksheet Functions 4 February 21st 05 08:11 PM
Today's Date Jake Excel Discussion (Misc queries) 9 December 17th 04 10:57 PM


All times are GMT +1. The time now is 03:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"