How to?

[Rokec]

Hi there...

Im new to the forums and indeed to the world of Excel.....so i've got a question for y'all....

I have excel file which has data of people in it....various details like name, location, adress, age, etc....

Now the problem is...the people working this job before me were sloppy bastards and they listed age in two ways ie. 1974(year of birth) and 54 years(age) old.

My boss wants everything to be listed in 1 way ie. year of birth....

Is there any sort of function that will allow to do this on the fly...because doing it by hand would be a major pain....something like 15000 entries in the files all together and on average 1/3 has the age format entered incorrectly

Thanks in advance

[SteveG]

rokec,

Insert a column to the right of your data. Assuming it starts in A2 then in B2 enter

=IF(A1<1000,(2006-A1),A1)

This will keep any data that is already in a birth year format as is and convert ages to the current year minus their age. Copy the formula down as needed. Once you are done you can Edit>Copy your new data and Edit>Paste Special> and select the Values option over the old. Delete the helper column.

HTH

Steve

[Dav]

You can have a guess, but the age does not tell you a year of birth, it could be one of 2 years. If you are 40 now were you born in 1965 or 1966 and have not had your birthday yet

Your guess

insert a column next to your Age/dob column

If the first age is in cell c2, in the cell next to it (D2) type =if(c2<1000,2006-c2,c2)

then copy this formula down for all the people

you can then select the column copy it and pastespecial as values to loose the formulas

Regards

Dav

[Rokec]

Hi guys...


Thanks for the fast reply...but...it it returned a "formula error message" in both cases...

Just to clarify what i did: i opened up an empty excel chart...then in A2 i inserted a fictional number 37 which would represent the number to be converted into 19xx year of birth format.
Next step was pasting the appropriate formula* into their respective column/row (separately of course), then i highlighted the function bar with my cursor and pressed enter....and it returned an error.

*formulas were:=IF(A1<1000,(2006-A1),A1)
:=if(c2<1000,2006-c2,c2)

in the 2nd case i put my 37 number in C2

Im sorry for all this im just such a noob with excel

[Dav]

ok if the cells are formated as text it may cause the error try the following

if the data is in cell c2, in d2
=IF(C2*1<1000,2006-C2*1,C2*1)

or zip the excell file and post it here as an attachment ans someone can return it to you. Or a portion of the file that does not work

regards

Dav

[Rokec]

Hi Dav

Here's a test file....has four entries representing age....if you can come up with the appropriate formula you'll be my hero of the week

[Dav]

Do you want to attach the file again. It is corrupted or you have not put your spreadsheet in the zip file. Well thats what this PC is showing, I will try another one when I get home, but perhaps you should check.

Regards

Dav

[Rokec]

Hi folks...

Ive found out the answer...it was pretty simple (like all things in life):

=Current year-the field ithe age is in OR =2006-m1

Thanks y'all