IF Function

[Reenee]

Can I calculate if a person was 21 at the date of 1/1/08 or 7/1/08?

Also, can you tell me how to convert SSN numbers that were imported from a
database to a number format and no matter what I do by trying to format the
cells it does not change. I have to go in each cell and take out the hyphens
but I have over 4K rows.

I have tried right clicking and format cells, special paste and nothing.

SSN LAST NAME FIRST NAME BIRTHDATE
123-45-5678 SMITH JON 10/14/1977
123-45-5679 DOE JANE 02/21/1982
123-45-5680 DOE JON 01/31/1980
123-45-5681 SMITH JANE 07/27/1980
123-45-5682 SMITH SARAH 02/24/1980
123-45-5683 DOE ANDREW 01/24/1988
123-45-5684 DOE STEVEN 07/07/1985
123-45-5685 DOE SANDRA 07/08/1989

 

[T. Valko]

Not sure how you want to handle the age on "x" or "y" issue.

One way would be to enter those dates in cells:

E1 = 1/1/2008
F1 = 7/1/2008

With your birth dates in C2 on down...

Enter this formula in E2 and copy across to F2:

=DATEDIF($C2,E$1,"y")

Then select both E2 and F2 and copy down as needed.

As far as the SSN issue...

Select the range of cells that contain the SSNs
Goto the menu Edit>Replace
Find what: -
Replace with: nothing, leave this empty
Replace All

I don't know if SSNs start with leading 0s but if they do those leading 0s
will get stripped off. Excel doesn't like numbers with leading 0s.

 

[DILipandey]

Hi Try using below:-

supposing you have your data in columns A to D, please enter following in
column E and Column F.

for column E:
=IF(OR(DATEDIF(D2,"1/1/2008","y")=21,DATEDIF(D2,"7/1/2008","y")=21),"Yes","No")


for column F: =VALUE(SUBSTITUTE(A2,"-",""))

And drag them down.

Now in column E, if the age comes to 21, it will show as Yes and also you
have the SSNs as numbers in column F.

Suggestion: Please do not share the SSNs like this.. thanks

dilipandey

--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India