Calculate the # of months between two date fields

[Cupcake]

I have 3 fields on a form. Two of those fields are date fields and the other is a Single line text field.

What I am looking for is take the two date fields for example if the Incurred Period From is December 2016 and the Incurred Period To is May. I want the # of adjustments field to have the calculated value of months from December to May.

Thanks,

Karen

[Kalilovenox]

no need to do condition.

total_month= (secondDateMonth - firstDateMonth )+ (secondDateYear - firstDateYear) * 12 ;

[AB07]

Hello,

You can create a business rule for this. Replace the "start_date" and "end_date" with the field names of "Incurred period from and Incurred period to" and "u_planned_duration" with the field name of   "# of adjustments"

Condition: current.start_date.changes() || current.end_date.changes() || current.work_start.changes() || current.work_end.changes()

Script:

//Calculate Planned duration

if(current.start_date.changes() || current.end_date.changes()){

  //If either field is empty blank the duration

  if(!current.start_date || !current.end_date){

  current.u_planned_duration = '';

  }

  else{

  current.u_planned_duration = gs.dateDiff(current.start_date.getDisplayValue(),current.end_date.getDisplayValue(),false);

  }

}

Regards,

Sahithi

[gyedwab]

Hi Karen,

If in future, you're looking to do these sorts of calculations on the fly (rather than setting up a server-side javascript business rule), you (or your users) could use Explore Analytics to do calculations.

For this example, you could use calculation in a List Report on your table:

And then you can create a 'composite view' to use this virtual table in reports:

The report is live, drillable, and can be published to ServiceNow dashboards. If you're doing these calculations for reporting purposes, this may be a more scalable way to deliver these calculations without requiring code changes to SN.

[Cupcake]

Hi Guy,

        Thank you for replying; however, this calculation is inside of form that works with a backend table where the records are being written to after the form is submitted. So I am not sure how you solution would be helpful to my form. This information is very helpful from a reporting standpoint though.

Thanks,

Karen

[GP6]

This is quite rudimentary in that it takes in a string value as the date (easy to amend that) - but the logic here does work well.

var start_date = '10-09-2004';  //input date range string is in English dd-mm-yyyy - if this is not adheared to expect whacky wrong results :)
var end_date = '10/12/2006';  //input date range string is in English dd-mm-yyyy - if this is not adheared to expect whacky wrong results :)

gs.info('Count of Months Between Dates of ' + start_date + ' and ' + end_date + ' is: ' + countMonthsBetweenDates(start_date, end_date));

function countMonthsBetweenDates(start_date, end_date) {
    if (end_date.length.toString() > 10 || start_date.length.toString() > 10) { //more than 10 means date entered incorrectly
        return 'Invalid Date Entry - Too Many Characters';
    } 
    var startDateArray = start_date.includes("-") ? start_date.split("-") : start_date.split("/"); //allows flexibility in date format for either dashes or slashes
    var endDateArray = end_date.includes("-") ? end_date.split("-") : end_date.split("/");
    return countTotalMonths(parseInt(startDateArray[1], 10), parseInt(endDateArray[1], 10), parseInt(startDateArray[2], 10), parseInt(endDateArray[2], 10)).toString(); 
    //we have to use radix values in parseint on the line above https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0687726
}

function countTotalMonths(startMonth, endMonth, startYear, endYear) {
    if (endYear < startYear) { //if the end year is before the start year this is not valid.
        return 'Ending Year Cannot Be Before Starting Year';
    } else if (startYear == endYear) { //if the start year is equal to the end year then we are in the same year and the logic is...
        if (startMonth == endMonth) { //if the start month is the same as the end month then its a zero month duration
            return 0;
        } else if (startMonth < endMonth) { //if the start month is BEFORE the end month then we subtract the start month from the end month to get the count of months
                return endMonth - startMonth;
            } else if (startMonth > endMonth) { ////if the start month is AFTER the end month then we subtract the end month from the start month to get the count of months
                return 'Ending Month for the same year cannot be BEFORE the starting month';
            }
    } else { //if the years are not equal
        var remainingMonthsInStartYear = 12 - startMonth; //subtracting the start month from 12 months gives you the count of months in the START year.
        var countMonthsInTheNextYear = endMonth; // getting the end month integer is inherently the count of months in the ending year.
        if (Math.abs(endYear - startYear) == 1) { //this line of code returns TRUE if the selected end date is in the next sequential year from the start year.  As an example:
            /*
            if start year is 2021
            and end year is 2022
            2022 minus 2021 is equal to 1
            that means that we categorically know that the end year is in the next sequential year
            OR
            if start year is 1999
            and end year is 2000
            1999 minus 2000 is equal to -1 (notice the negative in -1 this is why we have to use 'Math.abs' (abs is 'absolute' which turns a negative into a positive))
            */
            return remainingMonthsInStartYear + countMonthsInTheNextYear; //so we add the remaining months from the first year to the remaining months in the second year and we done.
        } else { //if we are NOT in the next sequential year then we have more work to do...
            var countMonthsForAllYearsAfterThisYearMinusAYear = (Math.abs(endYear - startYear) * 12) - 12; // this line of code counts the number of months for every year AFTER the start year but then we subtract 12 months because we need to specifically count the remaining months in the last year.
            var countOfMonthsInTheFinalYear = endMonth;
            return remainingMonthsInStartYear + countMonthsForAllYearsAfterThisYearMinusAYear + countOfMonthsInTheFinalYear; // we then add the remaining months from the start year to all of the months from the following years
        }
    }
}