resources from my career
There will inevitably be age grouping and age range requirements for many of your reporting needs, and the know-how to quickly and accurately calculate a patient's age is crucial to developing trust among your customers and colleagues.
Sometimes it is critical to calculate the age in your SQL language before the data even gets exported from the source.
But if you have the source data (and it includes the date of birth!) you can easily create age and age group calculations in Access or Excel. If you will be using Access to join tables and create multiple reports from the same data set, you should consider calculating the age (and establishing any age groups or ranges) within your Access queries. Alternatively, you can add your Age field "on the fly" using a quick formula in Excel, before you begin work on your final reporting.
This article explains how to compute a patient's age using each of these tools.
AGE CALCULATION BASICS
There are two functions we use to calculate age in years. First, we calculate the number of days between the two dates and divide the number of days by the number of days in a year, to get the total age in years.
Second, we truncate the decimals from the result to get the age in years as it is commonly expressed; for example, we want the patient to be 34 until his/her 35th birthday.
For any of these formulas, you can use whatever end date you like, such as the visit date, a static/set date, or the dynamic current date.
A note about Excel tables
When writing formulas based on the fields in a dataset, the best practice in Excel is to create a Table out of your data. To create a table, each field (column) needs to be named. Then, you can simply find "Table" in the "Insert" menu. The range for your data should be pre-selected, if every column in your table has a name, and you can just click Ok. Otherwise, you can select the range for your table. In establishing your data table, excel creates references to each field using the field names (column headers) you provide. This makes referencing the correct field very easy. You can use any date field as the start and end dates for your Age formula. (You will also notice when your data is in an Excel table, you don't have to copy down formulas, they automatically fill in to the entire field.)
Excel functions needed for Calculating Age: DAYS and TRUNC
The DAYS function in Microsoft Excel simply counts the exact number of days between two dates, first the End Date, then the Start Date. For best practices you will need to determine a common "end date" for your data set. This could be today, a set date, or a field in your dataset.
Of course, we don't want the age in days, we want to see it in years. To convert the number of days to years, we must divide the number of days by 365.25. Not 365, not 366; just 365.25.
Dividing the number of days by 365.25 will express the result as a decimal. But this isn't how we generally see a person's age. To get the age in years, we need to remove the decimal places at the end using the TRUNC formula. By removing the decimal places using the TRUNC function, rather than rounding, we have the patient's correct age up until their next birthday.
The TRUNC function is rather basic and only requires two inputs, the field to truncate, and then how many decimal places you would like to see in your result. For a person's age in years, we don't want any decimal places, so we will use a zero (0).
To use Today's Date
If you want to know the patient's age as of "today" (and also want the age to update each day you use the data set), you must use a dynamic end date for the Age calculation, and that is where the TODAY() function comes in.
To use today's date for the formula, enter the End Date as the TODAY function, including the parentheses.
The complete formula would look like this: =TRUNC(DAYS(TODAY(),[@[Date of Birth]])/365.25,0)
The [@[Date of Birth]] syntax is for a field in a table. If you don't have your data arranged in a table, your formula will look more like =TRUNC(DAYS(TODAY(),B2)/365.25,0), where the Date of Birth is in Column B.
Now each time you refresh the formulas in your workbook, the age will update to the patient's age as of the current date!
To use a Date Field in your dataset
Use the same formula, but point the end date to your date field, such as "Entered on Date," the complete formula would be =TRUNC(DAYS([@[Encounter Date],[@[Date of Birth]])/365.25,0)
Again, this syntax assumes your data in in a table. Otherwise, your formula will simply reference the cells where the End Dates and Start Dates lie.
To use a Static Date
If you want to calculate age at a point in time that doesn't change, replace the end date with the static date in double quotes: =TRUNC(DAYS("06/30/2020",[@[Date of Birth]])/365.25,0). This will give you the patient's age as of 6/30/2020.
Calculating Age in Access using the SQL Editor
The logic for calculating age in Access is the same as in Excel, except the functions are different. There is no TRUNC function in Access, and the DATEDIFF() function we use in Access requires the Start Date to be listed first, then the End Date (which is backwards from the Excel DAYS function).
Instead of TRUNC, we use INT (short for integer), which works exactly like TRUNC([number],0).
The formula for calculating age in Access is INT(DATEDIFF("d",[My Table].[START DATE],[My Table].[END DATE])/365.25) as [Age]
To use a specific date
To use a standardized "End Date" such as the end of the reporting period, we will use the same formula as above, but replace the TODAY() function with your End Date in quotes. The complete formula would look like this:
INT(DateDiff("d",[My Table].[Date of Birth],#06/30/2012#)/365.25) as [Age]
This will give you the patient's age as of 6/30/2012.
To use the date from another field
To calculate the Age of a patient based on a different date for each record or patient, such as an encounter date or membership start date, both of these fields will need to be established in your data table.
The formula would then look like: INT(DATEDIFF("d",[My Table].[Date of Birth],[My Table].[Encounter Date])/365.25) as [Age]
I hope you found this useful in understanding how to calculate Age in Excel and Access, and how to apply the formulas to your own data!
If you're interested in more articles like this, write to me at squirrelcat.01 [at] gmail [dot] com