Calculating age between two dates issue

jrlschumacher

I am trying to calculate student's age in years & months based on a future date. If the birthdate occurs before the cut-off date, the formula I have works fine. If the birthdate is after the cut-off date the formula is not calculating correctly. I am not sure how to fix this:

Paul Newcome

Give this a try:

=INT(((MONTH(COD@row) - MONTH(DOB@row)) + ((YEAR(COD@row) - YEAR(DOB@row)) * 12) - IF(DAY(DOB@row) > DAY(COD@row), 1, 0)) / 12) + "y " + MOD((MONTH(COD@row) - MONTH(DOB@row)) + ((YEAR(COD@row) - YEAR(DOB@row)) * 12) - IF(DAY(DOB@row) > DAY(COD@row), 1, 0), 12) + "m"

jrlschumacher

This is the formula I have now

=(IF(YEAR(COD@row) - YEAR(DOB@row) > 1, YEAR(COD@row) - YEAR(DOB@row) + " y", IF(YEAR(COD@row) - YEAR(DOB@row) = 1, YEAR(COD@row) - YEAR(DOB@row) + " y", "")) + IF(MONTH(COD@row) - MONTH(DOB@row) < 0, 12 + MONTH(COD@row) - MONTH(DOB@row), MONTH(COD@row) - MONTH(DOB@row)) + " m")

Paul Newcome

What exactly is it doing wrong, and what should it be doing? Can you show a few "broken" rows and then explain what they should be?

jrlschumacher

https://community.smartsheet.com/discussion/comment/469218#Comment_469218

Hi Paul,

I actually found another error in the months part of the formula so here is the updated version:

With Formula -

=(IF(YEAR(COD@row) - YEAR(DOB@row) > 1, YEAR(COD@row) - YEAR(DOB@row) + " y ", IF(YEAR(COD@row) - YEAR(DOB@row) = 1, YEAR(COD@row) - YEAR(DOB@row) + " y ", "")) + IF(MONTH(COD@row) - MONTH(DOB@row) < 0, 12 + MONTH(COD@row) - MONTH(DOB@row), MONTH(COD@row) - MONTH(DOB@row)) + " m ")

Row one for example would be 3 years 10 months, not four. The formula is counting this child as already four on the cut-off date when they will turn 4 two months after the cut-off date. The same for row 4 & 5 as they are still 3 on the cut-off date of 9/1/25. Row 7 does not turn 4 until 22 days after the cut off date…but since that is a matter of days that may be different.

Paul Newcome

Give this a try:

=INT(((MONTH(COD@row) - MONTH(DOB@row)) + ((YEAR(COD@row) - YEAR(DOB@row)) * 12) - IF(DAY(DOB@row) > DAY(COD@row), 1, 0)) / 12) + "y " + MOD((MONTH(COD@row) - MONTH(DOB@row)) + ((YEAR(COD@row) - YEAR(DOB@row)) * 12) - IF(DAY(DOB@row) > DAY(COD@row), 1, 0), 12) + "m"

jrlschumacher

@Paul Newcome THANK YOU! that worked.

Paul Newcome

Happy to help. 👍️