Excel and Office RAQ

Calculate current age in whole years in Access


Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing


Knowledge measurement audit

Item bank services

Item design course








Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in


Home page

Q: I'm trying to calculate age in Access using this formula

        Age: (Date()-[SPBPERS_BIRTH_DATE])/365.25

This runs from a make-table query. The table is formatted as Double with zero decimal places with the intent of getting a whole number.

The result is 20.7583846680 instead of 20. Please help.

A: You're probably better off using the DATEDIFF formula, and wrapping it in a ROUND. This should do what you need.


Since ROUND will always round to the nearest integer, I subtract 0.5 at the end which has the effect of the expression always rounding down, which is what we tend to do with ages, of course. You could - if you prefer - simply wrap such a ROUND function around your existing formula.

I've used 365.2425 because it's a closer approximation, and with a centurial leap year just having passed, I have a notion that more accuracy is better, although I haven't been bothered to prove it makes a difference I'm afraid!



Back to other RAQ topics