How to extract age range with existing formula?

[jaksg]

Original Question: "I use Excel to store the names of clients, including their ages. Can Excel automatically calculate and update the age of a person?

System: Windows XP

An office colleague suggested "=DAYS360(J145,NOW())/360". The J145 cell will have the value "4-Apr-49". Although it works, Excel gives a pop-up window that says "formula results volatile".

Is this above fomula acceptable?

thanks"

Answer: "Hi,

Welcome to MrExcel!

Try,

=DATEDIF(J145,TODAY(),"Y")&" Years and "&DATEDIF(J145,TODAY(),"ym")&" Month(s)"

HTH"
=================================================

Another question: The formula works well. Now I have another related problem.
I also need to extract a range of ages of clients. For exampled, I need to extract those clients whose ages are from 40 years old to 50 years old. Is it easy to create a formula to extract ranges?

thanks

 

[Yogi Anand]

Hi jacksg:

For your additional question, please post a few rows of your data and also your expected result.

 

[jaksg]

Hi jacksg:

For your additional question, please post a few rows of your data and also your expected result.

using the suggested formula =DATEDIF(J145,TODAY(),"Y")&" Years and "&DATEDIF(J145,TODAY(),"ym")&" Month(s)" in cells L2, L3 and L4 for example, I get the following:

cell J2: 14-Jun-49
result in cell L2: 56 Years and 5 Month(s)

cell J3: 20-Apr-55
result in cell L3: 50 Years and 6 Month(s)

cell J4: 15-Mar-63
result in cell L4: 42 Years and 8 Month(s)

Q: if I want to preserve the results in cells L2 to L4, can a formula be created in another cell that extracts those within the age group 50 to 59, for example?

I modified the above formula to =DATEDIF(J2, TODAY(), "Y") and I can get the following result:

cell L2: 56
cell L3: 50
cell L4: 42

With this method I can extract the age group. This method is still better than previously where I manually enter the ages without formula. So, I am already better off.

The month in the age is useful because I can at a glance tell which clients are going to have a new birthday soon.

thanks

 

[Yogi Anand]

Hi jaksg:

I am still not clear how you want your result displayed ... however, let us look at one of the ways depicted in the following ...

Book1
	J	K	L	M	N
1
2	14-Jun-49	TRUE	50	60	TRUE
3	20-Apr-55	TRUE			TRUE
4	15-Mar-63	#N/A			#N/A
5
Sheet1

a single array formula in cells K2:K4 ...

=LOOKUP(DATEDIF($J2:$J4,TODAY(),"Y"),$L$2:$M$2)=50

shows TRUE for those in the age range of 50 to 60, FALSE or #N/A otherwise.

Alternately, the following non-array formula in cell N2 and then copied down may also be used...

=LOOKUP(DATEDIF($J2,TODAY(),"Y"),$L$2:$M$2)=50

 

[jaksg]

cell L2: 56 Years and 5 Month(s)
cell L3: 50 Years and 6 Month(s)
cell L4: 42 Years and 8 Month(s)
cell L5: 35 Years and 4 Month(s)
cell L6: 49 Years and 2 Months(s)

Is it possible to just to do a count of those in the age range of 50 to 59, for example?

For example, I need the following result:
I key-in in cell G1 the following: Age 50-59. Formula driven cell H1 gives me a simple count of 2.

thanks

I am already learning a lot just being in this forum!

 

[barry houdini]

If you have your date range bounds in separate cells, e.g. 50 in one cell, 59 in another then you can do something like this

Book1
	I	J	K	L
1
2		10-Jun-1949	56 years 5 month(s)
3		20-Jun-1955	50 years 4 month(s)
4		12-Mar-1963	42 years 8 month(s)
5		13-Jul-1970	35 years 4 month(s)
6		11-Sep-1956	49 years 2 month(s)
7
8		From	50
9		to	59
10		count	2
11
Sheet1

Formula in K10

=SUMPRODUCT(--(DATEDIF(J2:J6,TODAY(),"y")>=K8),--(DATEDIF(J2:J6,TODAY(),"y")<=K9))

 

[jaksg]

SUMPRODUCT formula works well. thanks