Applying date range

**remo** · 05-18-2009, 10:13 AM

Hi There

And if anyone can help out you will have saved my day!

To explain I have two sets of merged data. On one fixed column I have two different sets of peoples age

1 - by age grouip eg 25 - 34
2 - By birth year so 01/02/1983

I know that person who is born between 1975 and 1984 would fall into the age group 25 - 34

I want to convert all the birth years into age groups. Its a lot of data (30,000 rows +) in random order

Does anyone have any idea how I would set this up, as I really want to add "age group" to a pivot table I have

Any ideas/suggestions really appreciated

Cheers
Remo

**shg** · 05-18-2009, 11:34 AM

You could use DATEDIF to compute the age in years base on birth date, and VLOOKUP to translate that to age groups.

**JBeaucaire** · 05-18-2009, 11:37 AM

Have some sample data pointing out where you're wanting to do this? (Click GO ADVANCED and use the paperclip icon to attach a workbook sample with appropriate desired results shown).

**daddylonglegs** · 05-18-2009, 11:42 AM

Assuming you have dates in A2 down and a date in D1 (e.g. today's date) at which you want the age then try this formula in B2 copied down

=MAX(0,FLOOR(DATEDIF(A2,D$1,"y")+5,10)-5)&" - "&FLOOR(DATEDIF(A2,D$1,"y")+5,10)+4

**remo** · 05-18-2009, 12:15 PM

Thanks everyone for there suggestion, I ended up using Daddylonglengs fix as works for me

**daddylonglegs** · 05-19-2009, 09:15 AM

Hello remo, re your PM

My suggestion works for identical sized time ranges, i.e. all 10 years. For variable ranges you can use a LOOKUP type formula as shg suggested, e.g. try

=LOOKUP(DATEDIF(A2,D$1,"y"),{0,18,25,35,45,55,65,75},{"0-17","18-24","25-34","35-44","45-54","55-64","old","really old"})

the "array constant" {0,18,25,35,45,55,65,75} defines the "lower bound" for each age range and the second array constant {"0-17","18-24","25-34","35-44","45-54","55-64","old","really old"} gives the descriptions for each

You could also use a table, e.g. if the lower bounds were in J2:J9 and the descriptions in K2:K9 then the formula would be

=LOOKUP(DATEDIF(A2,D$1,"y"),J$2:K$9)

This second approach is recommended, especially if you might want to change the range sizes or definitions. You can then do that without altering the formula.

**remo** · 05-19-2009, 09:55 AM

This is great, Im so glad I encountered this problem as between all the suggestions I already have multiple other uses I can apply this to

Thanks again!!