Aug 31 2020 03:13 AM - edited Aug 31 2020 05:20 AM
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!
Aug 31 2020 03:48 AM
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.
Aug 31 2020 04:18 AM - last edited on Aug 31 2020 09:59 AM by Dylan Snodgrass
Aug 31 2020 04:18 AM - last edited on Aug 31 2020 09:59 AM by Dylan Snodgrass
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?
Aug 31 2020 04:57 AM
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.
Aug 31 2020 05:13 AM
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.
Aug 31 2020 05:18 AM
Dear @mathetes ,
Thank you so much for all the valuable advice; I truly appreciate it! Thank you also for calculating the ages for me:)
I think I overlooked the 2nd sheet with personal information when I posted; will double-check in future and remove my attachment ASAP.
Have a lovely day
Aug 31 2020 05:20 AM
Perhaps in your locale you shall use semicolons, not commas. Please check how it is in other formulas.
=DATEDIF(E3;G3;"Y")