Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating age at different points in time
Using 2007, I need to calculate exact ages based on both full and partial
dates that could range from the 1400's to current day. This is for a genealogy project. I would like to be able to enter a birth date into a cell which could be month-day-year if known or month-year if known or just year. In an adjacent column, say column 2, there would be a list of dates which would mark various points in time, like census dates, marriage, etc. These dates could also be month-day-year if known or month-year if known or just year. In column 3, 4, and 5, adjacent to column 2, I need formulas that will calculate the difference in time from birth date to the date in column 2 showing age in years, months, days (example: 35 years, 6 months, 24 days). I know this is rather complicated to explain, if needed I will gladly try to explain further. Thanks much in advance for your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating age at different points in time
I used this formula to calculate years, month,days between two dates. Give
it a try =+YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<M ONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" -- Frank K "xp" wrote: Using 2007, I need to calculate exact ages based on both full and partial dates that could range from the 1400's to current day. This is for a genealogy project. I would like to be able to enter a birth date into a cell which could be month-day-year if known or month-year if known or just year. In an adjacent column, say column 2, there would be a list of dates which would mark various points in time, like census dates, marriage, etc. These dates could also be month-day-year if known or month-year if known or just year. In column 3, 4, and 5, adjacent to column 2, I need formulas that will calculate the difference in time from birth date to the date in column 2 showing age in years, months, days (example: 35 years, 6 months, 24 days). I know this is rather complicated to explain, if needed I will gladly try to explain further. Thanks much in advance for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating averages but without the extreme/exceptional data points | Excel Discussion (Misc queries) | |||
Daylight Saving Time (DST) and calculating time difference. | Excel Programming | |||
calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
Calculating hours between two points in time | Excel Programming |