# How to extract age range with existing formula?

#### jaksg

##### New Member
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"

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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi jacksg:

Yogi Anand said:
Hi jacksg:

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

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

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!

SUMPRODUCT formula works well. thanks

Replies
6
Views
166
Replies
8
Views
445
Replies
4
Views
865
Replies
6
Views
1K
Replies
4
Views
483

1,207,438
Messages
6,078,561
Members
446,349
Latest member
Malroos7912

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back