Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically enter today's date as a static entry | Excel Worksheet Functions | |||
today's date | Excel Worksheet Functions | |||
Calculate Years of Service | Excel Worksheet Functions | |||
Calculat years married or age of someone on today's date | Excel Worksheet Functions | |||
Today's Date | Excel Discussion (Misc queries) |