addin or subtracting dates before 1900??

[amknorr]

I geneaology work we have to work with dates prior to 1900. How can I find
the age of a person born in 1893 who expired in 1960?

 

[Allen Browne]

See the Age() function here:
http://allenbrowne.com/func-08.html

You can pass in the death date as the 2nd parameter, e.g.:
? Age([BirthDate], [DeathDate])
If the person hasn't died yet, it returns their age as of today.

DateDiff() can calculate a difference in years, but you need to adjust if
the person has not had their birthday in the year they died.

 

[Pixie78]

I was just reading about this and made a sample for to see if it worked for
dates before 1900 and it does. Use DateDiff. Here is the code you would use
in a text box control source of your form or report.

=DateDiff("yyyy", [DOB], [DOE])

the "yyyy" will calculate the time is years. "d" Days and so on. DOB would
be the name of your field for Date of Birth and I used DOE for the name of
your field for Date of Expiration.
I used it just now to make sure it worked before I posted it to you and it
does. I put in several different dob's in the 1800's and all the date of
deaths over 1900 and it works great.

 

[Pat Hartman \(MVP\)]

Keep in mind that you will always want to display four-digit years in this
application since you are working with data that spans centuries.

 

[John W. Vinson]

I was just reading about this and made a sample for to see if it worked for
dates before 1900 and it does. Use DateDiff. Here is the code you would use
in a text box control source of your form or report.

=DateDiff("yyyy", [DOB], [DOE])

the "yyyy" will calculate the time is years. "d" Days and so on. DOB would
be the name of your field for Date of Birth and I used DOE for the name of
your field for Date of Expiration.

That will be close... but not absolutely accurate, at least by the traditional
use of "age". DateDiff actually counts year (or day, or second, or...)
boundaries, not full years; so if [DOB] were 12/31/1875 and [DOE] were
1/1/1876 - just a day later - the age would be reported as one year. Or if
[DOB] were 1/1/1931 and [DOE] 12/29/1931 - almost a year - you'ld still get 0.

You can correct for this with an expression:

DateDiff("yyyy", [DOB], [DOE]) - IIF(Format([DOE], "mmdd") > Format([DOB],
"mmdd"), 1, 0)


John W. Vinson [MVP]

 

[Pieter Wijnen]

Or simplified to

DateDiff("yyyy", [DOB], [DOE]) + (Format([DOE], "mmdd") > Format([DOB],
"mmdd"))

I love Boolean expressions <g>

Pieter

I was just reading about this and made a sample for to see if it worked
for
dates before 1900 and it does. Use DateDiff. Here is the code you would
use
in a text box control source of your form or report.

=DateDiff("yyyy", [DOB], [DOE])

the "yyyy" will calculate the time is years. "d" Days and so on. DOB
would
be the name of your field for Date of Birth and I used DOE for the name of
your field for Date of Expiration.

That will be close... but not absolutely accurate, at least by the
traditional
use of "age". DateDiff actually counts year (or day, or second, or...)
boundaries, not full years; so if [DOB] were 12/31/1875 and [DOE] were
1/1/1876 - just a day later - the age would be reported as one year. Or if
[DOB] were 1/1/1931 and [DOE] 12/29/1931 - almost a year - you'ld still
get 0.

You can correct for this with an expression:

DateDiff("yyyy", [DOB], [DOE]) - IIF(Format([DOE], "mmdd") > Format([DOB],
"mmdd"), 1, 0)


John W. Vinson [MVP]

 

[John W. Vinson]

Or simplified to

DateDiff("yyyy", [DOB], [DOE]) + (Format([DOE], "mmdd") > Format([DOB],
"mmdd"))

I love Boolean expressions <g>

That's what I used to use... until someone pointed out that in SQL/Server True
is +1 rather than -1. The IIF is at least platform independent!

John W. Vinson [MVP]

 

[Pieter Wijnen]

Didnt' know SQLServer had IIf, Format & DateDiff <g>
the statement will only be valid in VBA anyway

Pieter

Or simplified to

DateDiff("yyyy", [DOB], [DOE]) + (Format([DOE], "mmdd") > Format([DOB],
"mmdd"))

I love Boolean expressions <g>

That's what I used to use... until someone pointed out that in SQL/Server
True
is +1 rather than -1. The IIF is at least platform independent!

John W. Vinson [MVP]

 

[Pieter Wijnen]

and using Abs is always an option <g> - I Still love Boolean math
DateDiff("yyyy", [DOB], [DOE]) - Abs(Format([DOE], "mmdd") > Format([DOB],
"mmdd"))

It doesn't get really tricky 'till you move to Oracle (PL/SQL) That does
really have the concepts TRUE & FALSE

Pieter

Or simplified to

DateDiff("yyyy", [DOB], [DOE]) + (Format([DOE], "mmdd") > Format([DOB],
"mmdd"))

I love Boolean expressions <g>

That's what I used to use... until someone pointed out that in SQL/Server
True
is +1 rather than -1. The IIF is at least platform independent!

John W. Vinson [MVP]