Computer Hope
Software => Computer software => Topic started by: amsmit09 on September 24, 2008, 09:47:54 AM
-
Hi, Does anyone know how to convert Date of Births to an actual age in the column next to the DOB's?? I want to not have to manually figure out what each persons age would be currently for example in a spreadsheet of peoples DOB's.
Thanks!!!
-
DateDiff the current date with their Date Of Birth?
-
DateDiff ...
Where did you get that function? It's not in my Excel 2007.
Here's my solution for a person 30 years old:
DOB Current Date Age
9/15/1978 9/24/2008 12:11 30
With forumulas displayed:
DOB Current Date Age
28748 =NOW() =(B2-A2)/365.25
Note: I used 365.25 as the divider to account for leap years (http://en.wikipedia.org/wiki/Leap_year).
-
Where did you get that function? It's not in my Excel 2007.
Visual Basic For Applications. My mistake though- I thought that all the global VBA functions were available in formulas.
-
Okay that was a huge help thank you, but I have about 80 different DOBs going down a column, is there are way to just drag the formula down the age column while having the numbers of the rows change (i.e. in the formula =(b2-a2)/365.25, change the b2-a2 to b3-a3, without having to type the formula 80 times??
-
Okay that was a huge help thank you, but I have about 80 different DOBs going down a column, is there are way to just drag the formula down the age column while having the numbers of the rows change (i.e. in the formula =(b2-a2)/365.25, change the b2-a2 to b3-a3, without having to type the formula 80 times??
The Fill Down command. if you paste the selection in the topmost column, select that column to the end of your list. Then (in Excel 2003, anyway) select the Edit->Fill->Down command.
I just tested- and in my version, it also allows you to simply paste the formula. Excel adjusts the references accordingly.
-
Your a genious, you just saved me an hour a day!!! I got Excel for dummies and it didn't tell me all that! Thank you so much :)
-
Your Welcome! That's what we're all here for :)
-
Actually one last thing...is there a way to delete the column with the =NOW() date without ruining the ages. Or maybe a way to copy that column without the formulas. I have to use a certain format to import this into another program and it has to go column 1:DOB, Column 2: Age. Do you know what i mean?
Thanks again
-
Regarding the technique for copying formulas down, it can be several ways, like many tasks in Excel. In addition to The Fill Down command, dragging the "fill handle" is another way; see How to use the Auto Fill Options button in Excel (http://support.microsoft.com/kb/291359). Copy and Paste can also be used; multiple rows can be selected for the paste command.
-
Actually one last thing...is there a way to delete the column with the =NOW() date without ruining the ages. Or maybe a way to copy that column without the formulas. I have to use a certain format to import this into another program and it has to go column 1:DOB, Column 2: Age. Do you know what i mean?
Thanks again
Will that other program import directly from Excel, or do you need to save the Excel data in a different format before you can import? I believe you need to keep all formulas in your main Excel file; otherwise, you'll have to recreate it each time you want to update it.
If you only want import the age column, you could use Copy and Paste Special | Values to paste the values, not the formulas, from the age column to a separate Excel workbook. Then, you could save that file as Text (.txt) or Comma Separated Value (.csv), or whatever works best for the import procedure.
Edit: Just for the info, I'll be away from my computer for awhile.
-
Ok thank you Copy, Paste Special worked for me and then I was able to delete the other columns with the formulas. It was not the formulas behind the numbers that mattered, but the fact that I was deleting a column that the formula was using so then is messed up the Values.
Thanks!!!! ;D
-
so what if i need to convert the whole column for example
DOB Age
02/09/90 ?