Hello, Can someone recommend the best way of calculating age in years, mont

Hello,
Can someone recommend the best way of calculating age in years, months and days, by taking only a birth date / start date?

Taking into consideration leap years and difference in days of the month, as they might be 28,29,30 or 31 days.

Example input would be: 1/10/2014 [1st October 2014]
Result on 2/4/2015 [2nd April 2015] would be: 0 years 6 months 1 days

Thanks

OriginalPostID-144093

OriginalPostID-144093

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Lead Developer
    You're going to use the year(), month(), and day() functions to get the differences between the 2 dates. So you'll get this:
    local!yearDiff: 1,
    local!monthDiff: -6,
    local!dayDiff: 1

    Now, you'll check local!monthDiff. If it is negative, you subtract 1 from local!yearDiff, then add it to 12 (e.g. 12 + (-6)), and set that as the new month value. Then check local!dayDiff. If that is negative, you'll subtract 1 from local!monthDiff. Here's the tricky part, you need to figure out the number of days in the month immediately preceding the end date.

    To do this, use day(eomonth(ri!endingDate),-1). This will get the last day of the previous month. Add this to local!dayDiff (in your example you can skip all of the day-related calculation because local!dayDiff is positive.

    Now you have the 3 numbers, and all that's remaining is to concatenate them into a string:

    local!yearDiffFinal & " years " & local!monthDiffFInal & " months " & local!dayDiffFinal & " days"
Reply
  • 0
    Certified Lead Developer
    You're going to use the year(), month(), and day() functions to get the differences between the 2 dates. So you'll get this:
    local!yearDiff: 1,
    local!monthDiff: -6,
    local!dayDiff: 1

    Now, you'll check local!monthDiff. If it is negative, you subtract 1 from local!yearDiff, then add it to 12 (e.g. 12 + (-6)), and set that as the new month value. Then check local!dayDiff. If that is negative, you'll subtract 1 from local!monthDiff. Here's the tricky part, you need to figure out the number of days in the month immediately preceding the end date.

    To do this, use day(eomonth(ri!endingDate),-1). This will get the last day of the previous month. Add this to local!dayDiff (in your example you can skip all of the day-related calculation because local!dayDiff is positive.

    Now you have the 3 numbers, and all that's remaining is to concatenate them into a string:

    local!yearDiffFinal & " years " & local!monthDiffFInal & " months " & local!dayDiffFinal & " days"
Children
No Data