How to extract age range with existing formula?

jaksg

New Member
Joined
Nov 16, 2005
Messages
8
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi jacksg:

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

jaksg

New Member
Joined
Nov 16, 2005
Messages
8
Yogi Anand said:
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
JKLMN
1
214-Jun-49TRUE5060TRUE
320-Apr-55TRUETRUE
415-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

New Member
Joined
Nov 16, 2005
Messages
8

ADVERTISEMENT

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! :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,104
Members
412,441
Latest member
kelethymos
Top