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!
|
|
0
|
|
|
|
Reply
|
 Utf
|
3/3/2010 5:56:01 PM |
|
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)<MONTH(A1),DAY(B1)
>=DAY(A1)),12,IF(AND(MONTH(B1)>MONTH(A1),DAY(B1)<DAY(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!
|
|
|
0
|
|
|
|
Reply
|
Utf
|
3/3/2010 10:49:01 PM
|
|
|
1 Replies
401 Views
(page loaded in 0.028 seconds)
Similiar Articles: Calculating age at different points in time - microsoft.public ...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 gen... calculate age in query - microsoft.public.accessHow do I calculate elapsed time in a simple query when the starting time is before 00:00 and ... Calculating age at different points in time - microsoft.public ... Criteria for age calculation - microsoft.public.accessIFF(Format([Birthdate],"mmdd")>Format(Date(),"mmdd",1,0), with criteria of [Age in ... Calculating age at different points in time - microsoft.public ... Age Calculation - microsoft.public.accessHow to calculate the age of the applicants at the point of approval. ... I want to calculate the age in a query. I have tried different ... specific date in time ... Age ... Calculating Age in Access - microsoft.public.access.gettingstarted ...I want to calculate the age in a query. I have tried different ... for a long time - microsoft.public ... As Long Dim lngDiffDays As Long Dim ... How to: Calculate Age ... Calculating Elapsed Time - microsoft.public.access.queries ...A date time is store as a floating point number, but around 0, there is something different than ... Anyone know how to calculate total time in query if the total time ... What is the formula for calculating the age (Expiry Date ...How to Calculate Age Range From ... Formula for calculating the diffrence between two dates ... Calculating age at different ... eHow.com How to Calculate the Date & Time ... calculate date of birth with an end date and age - microsoft ...I want to calculate the age in a query. I have tried different criteria ... How to Calculate Age Range From Birth Date ... ... calculating start time, end time, start time ... Count Age Grouping - microsoft.public.access.queriesCalculate an age from a birthdate using a specific date in time ... ... want to calculate the age in a query. I have tried different ... How to Calculate Average Age | eHow ... Specific Age Query for a Date Range - microsoft.public.access ...... 60 or 65, for the entire year beginning January 1. Can anyone please point ... calculate age in query - microsoft.public.access... date in time ... calculate age in query ... Calculating age at different points in time - microsoft.public ...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 gen... Calculate duration between two dates with time - timeanddate.comThe Time Duration Calculator will calculate the time that has elapsed/difference between two dates with time. Date/Time: Calculate Age of a person - Welcome to the MVPs.org ...Date/Time: Calculate Age of a person. Author(s) Dev Ashish, Michel Walsh & Tim Walters (Q) How do I calculate the age of a person given his/her birthdate? Online age calculator, calculate your birthdayAge calculator. Know your age. Calculate your age in days, years, minutes, seconds. Just know how many days / hours / minutes have been passed since your time of birth. Calculating Averages | eHow.comKnowing how to calculate averages ... of the values is different. You say, "Huh?" Suppose you... How to Calculate Grade Point ... Future Average Age; How to Calculate Grade Point ... 7/19/2012 3:34:03 PM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
32 followers. 