Is there a way to calculate age in years and months (4 years, 11 months for example)?
I have been able to calculate the age in years, but so far have not seen any other support questions to answer whether this can be done in years and months. Appreciate any help towards a solution, thank you!
Best Answer
-
Try this one. I saved it from a post a while back that I can't find at moment, but edited to fit DOB.
=YEAR(TODAY()) - YEAR(DOB@row) - IF(DATE(YEAR(DOB@row), MONTH(TODAY()), DAY(TODAY())) < DOB@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH(DOB@row) < 0, 12 + MONTH(TODAY()) - MONTH(DOB@row), MONTH(TODAY()) - MONTH(DOB@row)) + " months " + IF(DAY(TODAY()) - DAY(DOB@row) < 0, 30 + DAY(TODAY()) - DAY(DOB@row), DAY(TODAY()) - DAY(DOB@row)) + " days"
Answers
-
Try this one. I saved it from a post a while back that I can't find at moment, but edited to fit DOB.
=YEAR(TODAY()) - YEAR(DOB@row) - IF(DATE(YEAR(DOB@row), MONTH(TODAY()), DAY(TODAY())) < DOB@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH(DOB@row) < 0, 12 + MONTH(TODAY()) - MONTH(DOB@row), MONTH(TODAY()) - MONTH(DOB@row)) + " months " + IF(DAY(TODAY()) - DAY(DOB@row) < 0, 30 + DAY(TODAY()) - DAY(DOB@row), DAY(TODAY()) - DAY(DOB@row)) + " days"
-
Thank you so much! That worked out great. Appreciate the quick help :)
-
I am circling back around to review things on my sheet as I continue to work on this and am realizing I have one error that I have so far had trouble fixing, if anyone has insight.
I am using the following:
=YEAR(TODAY()) - YEAR(DOB@row) - IF(DATE(YEAR(DOB@row), MONTH(TODAY()), DAY(TODAY())) < DOB@row, 1, 0) + " y " + IF(MONTH(TODAY()) - MONTH(DOB@row) < 0, 12 + MONTH(TODAY()) - MONTH(DOB@row), MONTH(TODAY()) - MONTH(DOB@row)) + " m " + IF(DAY(TODAY()) - DAY(DOB@row) < 0, 30 + DAY(TODAY()) - DAY(DOB@row), DAY(TODAY()) - DAY(DOB@row)) + " d"
Example - Line 1: Person should be 5y,11m,29 days but my output is 5y,0m,29d. Lines 3, 5, 7 are correct.
I tried changing some of the numbers around and if I do MONTH(DOB@row) < 1, 11 in row 2 of the code I get the correct age for line 1, but now incorrect age for the others where it adds 11 months to them.
Any suggestions on where I should be editing this to get the right age? Thank you!
