Excel help (select one field and put age in the next)

[sports_brah]

Is it possible to take the first column and to automatically get the age in the next column?

I'd do it by hand usually, but there is over 1,000 entries and I'm really busy!

Any help would be very much appreciated!

 

[rhysduck]

=INT((A1-A2)/365.25)

A1 being a cell in the worksheet with todays date
A2 being the cell with the DOB in



Edit: I see you dates in your work sheet are of not a normal format, for the above to work the DOB will need to be in the format 01/01/2011)

 

[muon]

scan text for "aged " string and take next 2 characters.

http://office.microsoft.com/en-gb/excel-help/find-findb-functions-HP010342526.aspx

 

[[TW]Fox]

Assuming date of birth is on Cell A1, in Cell B2 put:

=(RIGHT(A2,3))

then in cell C3 put:

=LEFT(B2,LEN(B2)-1)

This will put the number you are after in Cell C3.

It's a bit long winded and I'm sure there is an easier way though.

 

[Haircut]

I'd probably just go for something like

=MID(A1, LEN(A1)-2, 2)

In this case.
Assuming all ages you have are two digits anyway!

 

[sports_brah]

scan text for "aged " string and take next 2 characters.

http://office.microsoft.com/en-gb/excel-help/find-findb-functions-HP010342526.aspx

Assuming date of birth is on Cell A1, in Cell B2 put:

=(RIGHT(A2,3))

then in cell C3 put:

=LEFT(B2,LEN(B2)-1)

This will put the number you are after in Cell C3.

It's a bit long winded and I'm sure there is an easier way though.

I'd probably just go for something like

=MID(A1, LEN(A1)-2, 2)

In this case.
Assuming all ages you have are two digits anyway!

Both of these give the value 3) for the first one etc.

 

[[TW]Fox]

Both of these give the value 3) for the first one etc.

Yes, thats what the second part of mine does, strip the ) off.

I made a bit of a mess of the cell references though..

Assuming date of birth is on Cell A1, in Cell B1 put:

=(RIGHT(A1,3))

then in cell C1 put:

=LEFT(B1,LEN(B1)-1)

Try now

 

[Haircut]

Both of these give the value 3) for the first one etc.

Have you got spaces at the end?

This one will trim the spaces
=MID(TRIM(A1), LEN(TRIM(A1))-2, 2)

 

[PeterNem]

Just whack it all in one formula, assuming the format is always the same:

=LEFT(RIGHT(A2,3),2)

edit: of course, if you have anyone who isn't double digits years old this will fail! I'd not make a very good developer!

 

[sports_brah]

Yes, thats what the second part of mine does, strip the ) off.

I made a bit of a mess of the cell references though..

Assuming date of birth is on Cell A1, in Cell B1 put:

=(RIGHT(A1,3))

then in cell C1 put:

=LEFT(B1,LEN(B1)-1)

Try now

Same answer!

 

[sports_brah]

Just whack it all in one formula, assuming the format is always the same:

=LEFT(RIGHT(A2,3),2)

edit: of course, if you have anyone who isn't double digits years old this will fail! I'd not make a very good developer!

No luck with this one!

Have you got spaces at the end?

This one will trim the spaces
=MID(TRIM(A1), LEN(TRIM(A1))-2, 2)

We have a winner! Excellent stuff, thanks. That just saved me an hour!

 

[PeterNem]

Again, only works if you don't have anyone aged 9 or 109!

edit: this works for varying number of digits in age:

=LEFT(RIGHT(A2,LEN(A2)-(FIND("aged",A2,1)+4)),LEN(RIGHT(A2,LEN(A2)-(FIND("aged",A2,1)+4)))-1)


(probably neater ways to do it)

 

[sports_brah]

Again, only works if you don't have anyone aged 9 or 109!

It's grand, they are all teenage upwards with no outliers of greater than 99.


All the data is in batches of 25 with a space underneath them. Going to calculate the average year, which will then be used as a dummy variable as a calculation of a result. Hopefully when I create the average, I'll just copy and past it for each batch and just change the cell references.

Last night will be long!



Thanks again for all your help everyone, some extremely clever guys here! Out of curiosity, did you learn your stuff through courses, or is most of it accumulated through time?

 

[Haircut]

Just whack it all in one formula, assuming the format is always the same:

=LEFT(RIGHT(A2,3),2)

edit: of course, if you have anyone who isn't double digits years old this will fail! I'd not make a very good developer!

One of the rules of being a developer is YAGNI
Don't waste time making it work for single digit or three digit ages if it's not necessary.

 

[james.miller]

=SUBSTITUTE(MID(A1,FIND("(aged",A1,1)+6,100),")","")

 

[muon]

What I would do is actually strip the bit at the end and then we'd actually have a DOB excel would understand. Then you could calculate whatever you wanted.