Hi guys,
We need to calculate the age of a person based on the completion of two date fields. In testing it works fine with the following formula until the Date of Birth ("DOB") is prior to 1 Jan 1969:
=DATEDIF(DOB,Date,"y")
(I've got an IF statement as well to avoid a blank field error, but that's the primary formula.)
I'm in NZ so am formatting the date fields as "dd-MMM-yy" in order for the calculations to work. For the following examples, I'm using today's date in the "Date" field (14 Mar 2017).
If I set the DOB to 1 Jan 1969 then the calculation returns "48" so that's fine. As soon as I make the date earlier than 1 Jan 1969 the calculation is wrong e.g. setting DOB to 31 Dec 1968 I get "-51".
If I configure the date fields to a Year Range of "100 years prior to 0 years in the future" I also get the error (when entering a date before 1 Jan 1969) of: "Value must be between 1917-01-01 and 2017-12-31".
I'm going to look for a different function than DATEDIF for age calculations.
Update: I've encountered the same issue using the FLOOR function: =FLOOR((Date-DOB)/365.25,1))
Update 2: The SUM function fails pre 1 Jan 1969 as well: =SUM((Date-DOB)/365.25)
Cheers,
Mike