Calculate Months between two Fields

[Daniel_12]

Hi there,

hope anyone could help here.

I have 3 Fields

1st Field is the Start Date wich you could pick with a Calender and its Formated in mm/yyyy

2nd Field is the End Date wich you could pick with a Calender and its Formated in mm/yyyy

and the 3rd Field is the Field where the Calcualtion should write the Months betweet this two Fields

Example:

Begin: 02/2019

End: 05/2020

MonthsTotal: 16

The starting and ending Months also count.

Begin Field is named begin

End Field is named end

Thank you

Best Regards

Daniel

[bebarth · Accepted solution]

Hi,

Here is my solution. This script must be placed in calcul for the target field:

var dB=this.getField("begin").valueAsString;
var nB=dB.indexOf("\/");
var dE=this.getField("end").valueAsString;
var nE=dE.indexOf("\/");
if (dB!="" && dE!="") {
var mB=Number(dB.substring(0,nB));
var yB=Number(dB.substring(nB+1));
var mE=Number(dE.substring(0,nE));
var yE=Number(dE.substring(nE+1));
if (yE<yB) {
var yE=[yB, yB=yE][0];
var mE=[mB, mB=mE][0];
}
var delta= ((yE-yB-1)*12)+(12-mB)+mE+1;
event.target.value=delta;
} else {
event.target.value="";
}

You will find an example file at:

https://wetransfer.com/downloads/6e1106414d4d0779b8979c925839420420191216160548/b6d3cb5dd9e7f872a658...

@+

[gkaiseril · Accepted solution]

I would convert the start and end date vstring values to the number of months and then set the field value to the difference plus 1.

event.value = "";
// get input values;
var cBegin = this.getField("begin").valueAsString;
var cEnd = this.getField("end").valueAsString;
// compute only if we have both dates;
if(cBegin != "" && cEnd != "") {
// convert dates values to an array of month and year;
var aBegin = cBegin.split("/");
var aEnd = cEnd.split("/");
// convert years to months and add to months;
var nBeginMonths = Number(aBegin[0]) + (aBegin[1] * 12);
var nEndMonths = Number(aEnd[0]) + (aEnd[1] * 12);
// compute difference and add start month;
var nDiffMonths = nEndMonths - nBeginMonths + 1;
// set field value;
event.value = nDiffMonths;
}

[Daniel_12]

Thank you this works like it should 🙂