Display age range and age based on date entered and actual calendar date.

[RodneyW]

If a column has a date, in this format dd.mm.yyyy is there a formula that will produce their age range in another column and their specific age in yet another.

For example, cell M10 has the date, let's say it's 5.4.1999. I'd like N10 to show 18-24 and O10 to show 24. Then when the calendar moves to 5.4.2024, the day of the person's birthday, I'd like N10 to change to 25-34 and O10 to change to 25.

Age ranges I need are 18-24, 25-34, 35-44, 45-54, 55-64 and 65+.

Thanks in advance.

 

[dreid1011]

Perhaps something like this (my date values are mm-dd-yyyy, and col O formatted as General because Excel likes to convert to date):

Book1
	M	N	O	P	Q	R
9		Range	Age		Lookup Table
10	5/3/1999	18-24	24		18	18-24
11	5/2/1999	25-34	25		25	25-34
12	5/3/1989	25-34	34		35	35-44
13	5/2/1989	35-44	35		45	45-54
14	5/3/1979	35-44	44		55	55-64
15	5/2/1979	45-54	45		65	65+
16	5/3/1969	45-54	54
17	5/2/1969	55-64	55
18	5/3/1959	55-64	64
19	5/2/1959	65+	65
20	5/3/1949	65+	74
21	5/2/1949	65+	75
Sheet1

Cell Formulas
Range	Formula
N10:N21	N10	=LOOKUP(INT((TODAY()-M10)/365.25),$Q$10:$Q$15,$R$10:$R$15)
O10:O21	O10	=INT((TODAY()-M10)/365.25)

 

[RodneyW]

Perhaps something like this (my date values are mm-dd-yyyy, and col O formatted as General because Excel likes to convert to date):

Book1
	M	N	O	P	Q	R
9		Range	Age		Lookup Table
10	5/3/1999	18-24	24		18	18-24
11	5/2/1999	25-34	25		25	25-34
12	5/3/1989	25-34	34		35	35-44
13	5/2/1989	35-44	35		45	45-54
14	5/3/1979	35-44	44		55	55-64
15	5/2/1979	45-54	45		65	65+
16	5/3/1969	45-54	54
17	5/2/1969	55-64	55
18	5/3/1959	55-64	64
19	5/2/1959	65+	65
20	5/3/1949	65+	74
21	5/2/1949	65+	75
Sheet1

Cell Formulas
Range	Formula
N10:N21	N10	=LOOKUP(INT((TODAY()-M10)/365.25),$Q$10:$Q$15,$R$10:$R$15)
O10:O21	O10	=INT((TODAY()-M10)/365.25)

Thank you, this is great and we're very close. In some case, column M is empty. When that is the situation, columns N and O should be empty. I've tried this:

=IFERROR(LOOKUP(INT((TODAY()-M11)/365.25),$Q$10:$Q$15,$R$10:$R$15),"")
and
=IFERROR(INT((TODAY()-M11)/365.25),"")

which produce values of 65+ in column N and 124 in column O

How can I modify so that the result is an empty cell if M is empty?

 

[dreid1011]

=IF(M10="","", formula)

 

[Dave Patton]

Dates Time.xlsb
	M	N	O	P	Q	R
7
8	Today	02-May-24
9		Range	Age		Lookup Table
10	05-Mar-99	25-34	25		18	18-24
11	05-Feb-99	25-34	25		25	25-34
12	05-Mar-89	35-44	35		35	35-44
13	05-Feb-89	35-44	35		45	45-54
14	05-Mar-79	45-54	45		55	55-64
15	05-Feb-79	45-54	45		65	65+
16	05-Mar-69	55-64	55
17	05-Feb-69	55-64	55
18	05-Mar-59	65+	65
19	05-Feb-59	65+	65
20
21	05-Feb-49	65+	75
5d

Cell Formulas
Range	Formula
N10:N21	N10	=IF(M10,LOOKUP(O10,$Q$10:$R$15),"")
O10:O21	O10	=IF(M10,DATEDIF(M10,$N$8,"y"),"")

 

[RodneyW]

=IF(M10="","", formula)

That got it. THANK YOU

 

[dreid1011]

You're welcome, and happy to help.