Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function
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 EditReplace 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. -- Biff Microsoft Excel MVP "Reenee" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |