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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,805
Messages
5,833,773
Members
430,232
Latest member
Testsubject

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top