Excel help

[bpesta22]

Greetings

I have an excel file with birthdays in a column.

They are typed in like this:

1955-08-07

Is there any easy way to change the format here to get each person's age (just by year would be fine?) listed in it's own column? Even just the year (e.g., 1955) would be awesome.

I need to calculate the average age of people in the spreadsheet, but have no idea how to do it when the birthdays are formated like this.

Thanks if you can help, the file is large so doing it by hand would suck.

[Darat]

Have you tried to format the column as dates?

ETA: In Excel 2003 it seems to format that date as a date Ok - then you can use the year function to subtract the birthday from today's date to get the years

[bpesta22]

I did look at that darat, but i have zero experience using excel.

Best I could tell, excel would only let me do it in the reverse oder, and without dashes.

8/7/1955

but my data are entered in with the year first and dashes

1955-8-7

:(

Any help would be appreciated.

[Darat]

What version of Excel are you using?

[skepHick]

If you want the age strictly by year, with no consideration to the month and day, this formula should do it:

Assume 1955-8-7 is in cell "A1"
In a blank cell, type

=(2006-(Left,A1,4))

Copy the forumla and paste it down the entire blank column.

If you want it to account for their exact age with consideration to the month and day, let me know and I'll probably be able to think of an appropriate formula in a bit (I don't have Excel on this old paperweight of a home compy, so it will take a few.)

ETA: In case you already read this, I accidentaly typed "row" instead of "column." I edited it above.

[bpesta22]

skepHick;1379326]If you want the age strictly by year, with no consideration to the month and day, this formula should do it:

Assume 1955-8-7 is in cell "A1"
In a blank cell, type

=(2006-(Left,A1,4))

Copy the forumla and paste it down the entire blank column.

If you want it to account for their exact age with consideration to the month and day, let me know and I'll probably be able to think of an appropriate formula in a bit (I don't have Excel on this old paperweight of a home compy, so it will take a few.)


Ack, probably because I am an idiot, it ain't working.

I just have one column now-- the A's. It's just the birthdate. I tried typing that formula in B1 and it keeps saying invalid / error message.

:(

[skepHick]

Well, you being an idiot aside - :p

That shoulda worked. I'll sign into Paltalk, it'd be easier work through this via IM.

[bpesta22]

Well, you being an idiot aside - :p

That shoulda worked. I'll sign into Paltalk, it'd be easier work through this via IM.


Cool, brt--- what are you wearing!

[bpesta22]

I'm here. I'm guessing we're gonna need web cams to communicate clearly and what not (I will let you know if your cleavage is distracting, and you let me know if my cleavage is distracting)?

Let me light some candles!

[skepHick]

Ha! Nice try, zippy! You want help, or not? :p

[bpesta22]

yes, but I am on and waiting. where'st are you?

[Kadesh]

=(2006-value(Left,A1,4))
should do the trick

If you want to convert the birthdays you already have in there to a valid date use: =datevalue(a1)

you can then copy that down for all the dates, then copy the column and do a paste-special and select values to change the formula to the value then you can delete the original column if you no longer need it.

From there you can just use =INT((NOW()-A1)/365)
this will update the age whenever you open the workbook.

[bpesta22]

=(2006-value(Left,A1,4))
should do the trick

If you want to convert the birthdays you already have in there to a valid date use: =datevalue(a1)

you can then copy that down for all the dates, then copy the column and do a paste-special and select values to change the formula to the value then you can delete the original column if you no longer need it.

From there you can just use =INT((NOW()-A1)/365)
this will update the age whenever you open the workbook.


Arg, it aint working. I keep getting an error box saying invalid format. Me and skepchick had the same problem.

If I just type in: =a1

it will repeat what was in a1, but

using anything more sophisticated spits out the error message

:(

[Kadesh]

Is there an error number associated with the message?
Is there any sort of conditional formatting on the cells you're putting the formulas into?

[Chris Haynes]

It might be helpful if you split the columns ups.

Select the column with the dates. Select the drop-down menu of "Data". From that select "Text to Columns". Since your dates are deliminated with dashes, it should split into seperate columns that you can create a simple formula with.

Follow the directions (I'm testing it with a file that has phone numbers). In the wizard the second page allows me to choose what the delimiter is... I changed it to a "-". That worked. Except it failed for me because I did have some merged cells (for stupid extra area codes). I've used this tool to seperate last names from first names (I used to create school directories, until I figured out that it would be better to let someone else do that).

With luck you can change the "Birthdate" column into three columns: Year, Month, Day.

[bpesta22]

It might be helpful if you split the columns ups.

Select the column with the dates. Select the drop-down menu of "Data". From that select "Text to Columns". Since your dates are deliminated with dashes, it should split into seperate columns that you can create a simple formula with.


Follow the directions (I'm testing it with a file that has phone numbers). In the wizard the second page allows me to choose what the delimiter is... I changed it to a "-". That worked. Except it failed for me because I did have some merged cells (for stupid extra area codes). I've used this tool to seperate last names from first names (I used to create school directories, until I figured out that it would be better to let someone else do that).

With luck you can change the "Birthdate" column into three columns: Year, Month, Day.



It worked!

Thanks much. I would gladly name my first chitlen after you, but I've had him already, and have since snipped my thingies that produce the swimmers, and hence cannot breed anymore.

But, indeed, label me thankful!!!!

B

[69dodge]

If Excel knows they're dates, this should work: =now() - A1

If it thinks they're just text, this should work: =now() - date(value(mid(A1,0,4)), value(mid(A1,5,2)), value(mid(A1,8,2)))

The second assumes that all months and days are two digits, like 2006-01-14 not 2006-1-14. If not, and if it's ok to pretend that all the birthdays are July 1 of the correct year, you can use this instead: =now() - date(value(mid(A1,0,4)), 7, 1)

All of these formulas give the age in days.

[Chris Haynes]

:)

[Paul C. Anagnostopoulos]

I would have exported a CSV file, edited it, and reimported. Shame on you guys for actually using the features of Excel!

~~ Paul