Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Calculate age (years and months) from two dates

OlyChristine
Level 1
Level 1

Am trying get the (near accurate) age in years and months to populate in "Age on date of retirement" (Age1) from the date of birth (DOB1) and the date they retired (retireDate1). The only examples I've been able to find are for "today" dates. Can someone help with code? And I understand it works best with JavaScript but would FormCalc work also?

Dates are set as MM/DD/YYYY

Age field is num{z9}

Thanks in advance for your help!

1 Accepted Solution
jasotastic81
Correct answer by
Level 7
Level 7

I was able to make this work with Age1 as a text field. Of course, you can do whatever you want with it, but this method made the most sense to me since I can say "99 years 99 months". If you need to separate the year and month into different fields, you should be able to do so easily. Lastly, this requires that the user does not manually enter the dates. (Something about the raw values isn't quite right when they're entered versus being selected.)

Age1::calculate (or wherever you'd like to have this event)


if (!DOB1.isNull && !retireDate1.isNull){


  var d1 = DOB1.rawValue.split('-');


  var d2 = retireDate1.rawValue.split('-');


  var years = parseInt(d2[0]) - parseInt(d1[0]);


  var months = (parseInt(d2[1]) - parseInt(d1[1]));


  if (parseInt(months) < 0){


    months = 12 + months;


    years--;


  }


  Age1.rawValue = years + " years " + months + " months.";


}


View solution in original post

6 Replies
jasotastic81
Correct answer by
Level 7
Level 7

I was able to make this work with Age1 as a text field. Of course, you can do whatever you want with it, but this method made the most sense to me since I can say "99 years 99 months". If you need to separate the year and month into different fields, you should be able to do so easily. Lastly, this requires that the user does not manually enter the dates. (Something about the raw values isn't quite right when they're entered versus being selected.)

Age1::calculate (or wherever you'd like to have this event)


if (!DOB1.isNull && !retireDate1.isNull){


  var d1 = DOB1.rawValue.split('-');


  var d2 = retireDate1.rawValue.split('-');


  var years = parseInt(d2[0]) - parseInt(d1[0]);


  var months = (parseInt(d2[1]) - parseInt(d1[1]));


  if (parseInt(months) < 0){


    months = 12 + months;


    years--;


  }


  Age1.rawValue = years + " years " + months + " months.";


}


View solution in original post

OlyChristine
Level 1
Level 1

I keep getting an error, I've set Age1 as a text field, and the other two are date/time.  Hmmm. I may just put this down for a bit and attack it again with a clear head. I am hoping that whatever code I use, the user would be able to enter the (formatted) date - MM/DD/YYYY in the date/time fields., but I'm flexible. 

I was trying to modify the below to fit my needs, but I know it's missing something, alas.

if (HasValue(DOB1)) then

  if (HasValue(retireDate1)) then

    var retireDate1_ = Date2Num(retireDate1.formattedValue, "MM/DD/YYYY")

    var DOB1_ = Date2Num(DOB1.formattedValue, "MM/DD/YYYY")

    var diff = retireDate1_ - DOB1_

    $.rawValue = Floor(diff / 365.25)

  else

    $.rawValue = null

  endif

else

  $.rawValue = null

endif

I'd be thrilled with just calculating years and forget about the months 😕

jasotastic81
Level 7
Level 7

Can the other two fields be changed to date fields instead of date/time?

What error are you getting with your current script?

jasotastic81
Level 7
Level 7

Also, I used your script and set the event language to formCalc. It works just fine to get the years. I think there's just some coding logic + math required to get the leftover bits from what floor rounded off and multiply by 1200 (or something).

jasotastic81
Level 7
Level 7

if (HasValue(DOB1) and HasValue(retireDate1)) then


  var retireDate1_ = Date2Num(retireDate1.formattedValue, "MM/DD/YYYY")


var DOB1_ = Date2Num(DOB1.formattedValue, "MM/DD/YYYY")


var diff = retireDate1_ - DOB1_


var years = Floor(diff / 365.25)


var months = Floor(((diff / 365.25) - Floor(diff / 365.25)) * 15.21)


$ = Concat(years, " years ", months, " months.")


else


  $ = null


endif


OlyChristine
Level 1
Level 1

GENIUS!!! At first it didn't quite work for me, it showed zero values but did show this: 0 years 0 months. BUT then I looked at their patterns, one of the cells was not set for the MM/DD/YYYY format - uh der. I am so forever grateful to you and your brain. I may have one more problem I'm trying to work out, separate from this, but I'll start a different conversation.

Again, thank you!!