Calculating age between two dates issue
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:
Best Answer
-
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"
Answers
-
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")
-
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?
-
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.
-
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"
-
@Paul Newcome THANK YOU! that worked.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!