Greetings:
I have seen so much misinformation on the internet about age calculations.
Let's start with the leap year issue.
Microsoft date/time fields know precisely when leap years occur in the Gregorian calendar we use. If you try to enter Feb 29 in any year except a valid leap year, you will get an error message. So calculating the correct age from DOB has nothing to do with when leap years occur or how many have occurred since a given date. The MS date/time field already knows that.
It has to do, first, with the number of days per year, which must be an average, since the number of days is not the same for every year in our calendar: the first correction for this is leap years. Keep in mind that leap years fall in the years of US presidential elections. If there is a leap year once every four years you might think that the accurate number of days per year would be 365.25, since you add one day for every four years.
However, in the Gregorian calendar we use, adding a leap year every four years over-compensates for the true length of a year (now we are into the realm of astronomy and physics). Accordingly, once every 400 years, a year that would be expected to be a leap year is not. The years when leap year is skipped are those in which the first year of a century is evenly divisible by 400. So the first year of the 20th century, which was 1900, had no Feb 29, even though McKinley was elected President in that year.
The reason 1900 is the first year of the 20th century instead of, for example 1901, is another story, and it too has to do with the imposition of the rules of mathematics (mathematics demands a zero point in any ratio scale) on human life so as to make human life follow the rules of mathematics, which, of course, we invented. This process began in the 16th century, with the adoption of the Gregorian calendar in 1582, and was finalized in Europe during the 18th century. Many of us are are still catching up.
The only departure from this is that the Gregorian calendar, adopted by a Pope not a mathematician, does not in fact include a year of zero: it includes a year of one and a year of minus one--sounds like a programming issue to me. By default, 1 BC is the year of zero, and this of course has little to do with when Jesus may have been born. It is a left-over, historical artifact, stuffed into our more recent mathematical conception of the world.
MS date/time fields already know all of this. If you enter the date 2/29/1900 in a date/time field you will receive an error message: "your entry is invalid for the data type." If you try to enter the date 2/29/3000, the next year a leap year will be skipped, you will receive the same error message.
So the number of days since the DOB or any other date of interest needs to be divided by the correct number of days per year. That number is:
(400*365)+((400/4)-3)/400 = 365.2425.
There should be a 4,000 year rule too, because the 400 year rule will not be sufficient for more than, well, another millennium. But the Gregorian calendar does not include a 4,000 year rule. So in about 1,997.4167 years or so, the above calculation will be off by a day divided by 4000 years. The number of days in a year is also changing slowly as the solar system evolves, and further adjustments will be necessary eventually. Remember, there is no such thing as perfect measurement. Measurement is a human construct, which does not exist in the universe outside our minds.
The second major issue with age calculations is rounding, and this too has to do with the rules of mathematics.
When we speak in society, a person remains their current age in years until their next birthday. In speech, we are not 44.4 or 44.6, we are just 44 until we turn 45. Little children want to tell us more in order to make themselves look older (I am five and a half, said my son).
In mathematics, however, when we render a number with no decimal places it will be always be rounded up after 44.49999... . So if you include no decimal places, a person who is 44.49 will be rendered as 45, according to the rules of mathematics. In order to get the correct number of years according to the way we speak, you must include at least one decimal place.
However, if you do not specify how many decimal places you want, the default is 12, and this is the last issue with accurate age. Twelve decimal places is far too many for most purposes.
So here is the correct formula for 2 decimal places in a calculated field for a query:
AccurateAgeR:Round((Date()-[DOB])/365.2425,2)
Note that an age of 44.25 translates easily to 44 and 3 months, with a month equal to .08... years. It is cumbersome in most instances to try to include months themselves. When a report calls for "age," it is cleaner and easier to say 44.53, rather than 44 and 6.04 months. Tip: that is why we imposed the rules of mathematics on ourselves in the first place.
If you want to put the formula somewhere else other than a calculated field in a query, then the beginning, before the colon, needs to modified slightly.
David
