Business Rule to calculate duration not work

[Jason Nicholas]

Hi, I have a business rule where I am trying to calculate the following on a project:

1. Project During between start and end dates

2. Remaining Duration between now and project end date

Example start date 01/09/2025 00:00:00 & End Date 31/10/2025 00:00:00

The Project Duration is returning a figure of  (60 Days 16 Hours)

The Remain Duration figure changes every time I save but not in the correct way, its random (63 Days 23 Hours 36 Minutes then 63 Days 3 Hours 1 Minute then 63 Days 33 Minutes then 63 Days 22 Hours 49 Minutes)

Obviously my calculations are wrong somewhere, can anyone assist and point out where

(function executeRule(current, previous /*null when async*/) {

/**********************************************************/

/* Get todays date & start and end dates from the project */

/**********************************************************/

        var todaysDate = new GlideDate();

        var todaysDateSec = todaysDate.getNumericValue();

        var startDate = new GlideDateTime(current.start_date);

        var startDateSec = startDate.getNumericValue();

        var endDate = new GlideDateTime(current.end_date);

        var endDateSec = endDate.getNumericValue();

/******************************************************************************************************************/

/* Calculate difference in seconds between start and end dates & also difference between todays date and end date */

/******************************************************************************************************************/    

        var dateDiff = (endDateSec - startDateSec);

        var dateRemain = (endDateSec - todaysDateSec);

/****************************************************/

/* Set up the variables for Days, Minutes and Hours */

/****************************************************/

        var secondsInAMinute = 60.0;

        var secondsInAnHour = 60.0 * secondsInAMinute;

        var secondsInADay = 24.0 * secondsInAnHour;

/****************************************************************************/

/* Calculate Date Difference between Planned Start and End dates of Project */

/****************************************************************************/

        var durationSeconds = parseInt(dateDiff, 10);

    // Extract Days and round up to whole number

        var durDays = Math.floor(durationSeconds / secondsInADay / 1000);

    // extract hours

        var durHourSeconds = durationSeconds % secondsInADay;

        var durHours = Math.floor(durHourSeconds / secondsInAnHour);

    // extract minutes

        var durMinuteSeconds = durHourSeconds % secondsInAnHour;

        var durMinutes = Math.floor(durMinuteSeconds / secondsInAMinute);

    // extract the remaining seconds

        var durRemainSeconds = durMinuteSeconds % secondsInAMinute;

        var durSeconds = Math.ceil(durRemainSeconds);

    // Convert days to string to add to duration field

        var duration = (durDays.toString() + ' ' + durHours.toString() + ":" + durMinutes.toString() + ":" + durSeconds.toString());

/**********************************************************************************/

/* Calculate Date Difference between Planned End dates of Project and todays Date */

/**********************************************************************************/

    //var rem_time= dateRemain;

        var remainSeconds = parseInt(dateRemain, 10);

    // Extract Days and round up to whole number

        var remDays = Math.floor(remainSeconds / secondsInADay / 1000);

    // extract hours

        var remHourSeconds = remainSeconds % secondsInADay;

        var remHours = Math.floor(remHourSeconds / secondsInAnHour);

    // extract minutes

        var remMinuteSeconds = remHourSeconds % secondsInAnHour;

        var remMinutes = Math.floor(remMinuteSeconds / secondsInAMinute);

    // extract the remaining seconds

        var remRemainingSeconds = remMinuteSeconds % secondsInAMinute;

        var remSeconds = Math.ceil(remRemainingSeconds);

    // Convert days to string to add to duration field

        var remain = (remDays.toString() + ' ' + remHours.toString() + ":" + remMinutes.toString() + ":" + remSeconds.toString());

        current.u_project_duration = duration;

        if (dateRemain > 0){

            current.u_days_remaining = remain;

        }

        if (dateRemain <= 0){

            remain = ('0'.toString());

            current.u_days_remaining = remain;

        }

})(current, previous);

[Mark Manders]

Duration is already calculated on the project itself, right? 

And why not let ServiceNow do the work for you? It's not meant for this kind of requirements, but the SLA engine has a 'time left' calculation. If you just trigger SLAs on the project table with the planned end time as breach time, you should get the results you need.

[Jason Nicholas]

Hi

We don't have the duration calculated on the Project and the project does not have any SLA's. The fields are fed from our CRM system and are there for PMO to understand and help manage the projects without having to do manual calculation.

I know what I am doing can be done, I just need to understand where my calculations are incorrect and not working as expected.

[Ankur Bawiskar]

@Jason Nicholas 

try this, changes made

Issue 1: Using GlideDate for today's date

-> You're using GlideDate, which only gives the date without time. This will break logic when calculating the remaining duration, especially if the time of day matters. 

Issue 2: Incorrect unit conversion

-> You're dividing by 1000 after calculating days, which is wrong because getNumericValue() already returns milliseconds. Your secondsInADay is in seconds, so you need to convert milliseconds to seconds before using it.

(function executeRule(current, previous /*null when async*/) {

    var todaysDate = new GlideDateTime(); // includes time
    var todaysDateSec = todaysDate.getNumericValue();

    var startDate = new GlideDateTime(current.start_date);
    var startDateSec = startDate.getNumericValue();

    var endDate = new GlideDateTime(current.end_date);
    var endDateSec = endDate.getNumericValue();

    // Convert milliseconds to seconds
    var durationSeconds = Math.floor((endDateSec - startDateSec) / 1000);
    var remainSeconds = Math.floor((endDateSec - todaysDateSec) / 1000);

    var secondsInAMinute = 60;
    var secondsInAnHour = 3600;
    var secondsInADay = 86400;

    // Duration
    var durDays = Math.floor(durationSeconds / secondsInADay);
    var durHours = Math.floor((durationSeconds % secondsInADay) / secondsInAnHour);
    var durMinutes = Math.floor((durationSeconds % secondsInAnHour) / secondsInAMinute);
    var durSeconds = durationSeconds % secondsInAMinute;

    var duration = durDays + ' Days ' + durHours + ' Hours ' + durMinutes + ' Minutes ' + durSeconds + ' Seconds';

    // Remaining
    var remDays = Math.floor(remainSeconds / secondsInADay);
    var remHours = Math.floor((remainSeconds % secondsInADay) / secondsInAnHour);
    var remMinutes = Math.floor((remainSeconds % secondsInAnHour) / secondsInAMinute);
    var remSeconds = remainSeconds % secondsInAMinute;

    var remain = remDays + ' Days ' + remHours + ' Hours ' + remMinutes + ' Minutes ' + remSeconds + ' Seconds';

    current.u_project_duration = duration;

    if (remainSeconds > 0) {
        current.u_days_remaining = remain;
    } else {
        current.u_days_remaining = '0';
    }

})(current, previous);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Jason Nicholas]

Ankur

Thank you, generally this has worked although I did need to amend the duration and remain variables as follows or they would not populate the duration field on the form:

var duration = (durDays.toString() + ' ' + durHours.toString() + ":" + durMinutes.toString() + ":" + durSeconds.toString());

var remain = (remDays.toString() + ' ' + remHours.toString() + ":" + remMinutes.toString() + ":" + remSeconds.toString());

Odd thing now is that whilst it works for most, if the project timeline is a long one it adds extra time

The Duration should be 60 Days and the remaining should be 63 Days 8Hours 7 mins

Jason