Forum Discussion
Age Formula
Hi,
I'm looking for some help / advice.
I have a spreadsheet which hold details of patients I provide anaesthetic care for. Part of this information includes date of birth, and date of their procedure.
I've just tried to add a column to determine the patient's age at time of their procedure using the formula =DATEDIF(C3,G3,"Y") where C3 is DOB & G3 DOP.
This works fine until I have any patients aged 99 or older, when #VALUE! displays.
Thanks in anticipation, Rory
4 Replies
Hi Hans,
Thanks for reaching out. I tried your formula and ended up with the same result of "#VALUE!"
I formatted both the date of birth & date of op cells as custom "dd-mmm-yy". Excel will assume all years under '26 to be 2026, yet when I enter a 4 digit year the that is preserved. Except for the last 3 DOB in the attached extract of my 6 oldest patients at the time of their surgery: I suspect that is the source of the error. I have been unsuccessful in my attempts to get Excel to display those last 3 DOB as it does the first 3
It might be the size of the spreadsheet >17000 patients in it, but that doesn't really explain why some DOB display as entered and others don't.
Tried attaching the extract as an .xlsx but the upload is not supported. So 2 pictures uploaded: 1 displaying cell with your formula in, the other with a different cell with the original formula.
If you have the time, I'd be most grateful for any advice you can offer me.
Deleted
Please click the Excel menu and select Preferences.
Select Calculation.
Is the 'Use 1904 date system' check box ticked? If so, Excel cannot handle dates before January of 1904.
Warning: if you clear the check box, all existing (valid) dates will change!
That's strange - it works OK in Excel for Windows:
But try the following instead:
=YEAR(G3)-YEAR(C3)-(DATE(YEAR(G3), MONTH(C3), DAY(C3))>G3)
