Forum Discussion
Age formula not calculating
Good day, I am a beginner Excel user and am trying to calculate age between 2 different dates where B2 contains the date of diagnosis of a disease and E2 contains the date of birth. The column with my formula is formatted to "Number" and Calculation is set to Automatic. I have also done the text to Column thing. My date columns are set to the same date format.
I type in the following formula: =datediff(B2,E2,"M"), but I receive an error message that there is a problem with the formula (the part between brackets is then highlighted). Also, when I type "B2" that column is highlighted blue, but when I type "E2", that column is NOT highlighted red as I expected it to.
How can I fix this error and calculate the age? Would really appreciate your help!
8 Replies
You say that you're a beginner at Excel, so I'll be presumptuous and note that there are other things you could do that might make your overall spreadsheet easier to maintain. Certainly there are "tricks" you could learn that will be beneficial in the future.
For example, you have today's date appearing in every row, and then use that redundant data in the calculation of the ages of the patient referred to in that row. More efficient would be to have a separate cell off to the side,say in cell M1 and have that cell contain today's date. =TODAY()
(that "off to the side" could also be elaborated on in a later lesson.)
Then the age calculation in your cell C2, now reading =DATEDIF(E2,D2,"M") would become
=DATEDIF($M$1,D2,"M")
Those dollar signs make the reference a "absolute reference" instead of the "relative reference" of E2. Then, when you copy the formula down to all the lower rows, it stays $M$1, while the D2 becomes D3, D4,.....
That's the way to handle variables that are, in fact, semi-constant and that are referred to in multiple places in a spreadsheet.
I'd also recommend looking into Data Validation. It occurs to me that you may have downloaded the actual diagnoses (and possible all of the patient data)...but if you find yourself adjusting diagnoses, Data Validation could save typing the words and insure consistency.
Another formula you can use is
=(G2-E2)/365.25 (the .25 because that is the more precise number of days in a year, accounting for leap years)
That formula has been put into the attached copy of your spreadsheet.
Separate issue: the copy you posted contained information that should have been deleted before posting...names of patients. It's important that such private info NOT be posted. You can remove the sheet yourself, but if you don't get to it in time, I've alerted the board moderators to take it down. In the copy I'm attaching, the sheet with those names has been removed.
Double "f" is for the function in VBA, here is
=DATEDIF(E3,G3,"Y")or
=YEARFRAC(G2,E2,1)depend on what exactly you'd like to calculate.
Hi Sergei, thanks for your reply! I have tried using both your suggested formulas (copy and pasted them), but I still get a message that there is an error (see attached file).
Any other suggestions?
Perhaps in your locale you shall use semicolons, not commas. Please check how it is in other formulas.
=DATEDIF(E3;G3;"Y")
