Calculate difference between two date & time field excluding weekend and display the result in duration field

[farci]

Hi Team,

I am trying to calculate the difference between two date and time fields and get the result which excludes weekend and store the result in duration field.

This is done by me via a schedule job. which works fine but does not exclude the weekends.

For example if my start date/time and end date/time is 2-Aug-2018 15:04:00 and 21-Sep-2018 0:30:00 I should get approx result of 37 days few hours and time in duration field as my result but I get as 48 days few hours, min and seconds in my result field(duration field)

below is my script.

var start = ' ';
var end = ' ';
var gr = new GlideRecord('table_name');
gr.addNotNullQuery('state_start');
gr.query();
while(gr.next())
{
var days = 0;
end = new GlideDateTime();
start = gr.state_start;
start = new GlideDateTime(start);
if(start.getDayOfWeekUTC()!=6 && start.getDayOfWeekUTC()!=7){
days++;
}
start.addDaysUTC(1);
var diff = GlideDateTime.subtract(start, end);
gr.state_open_age=diff ;
gr.autoSysFields(false);
gr.update();

}

Any help or suggestion is highly appreciated.

Regards,
Narmi

[shivaprasad3]

Hi ,

Could you please share your scheduled job where you have written the script to get the difference between two dates excluding the weekends.

Thanks in advance!!

Thanks and Regards,

Shivaprasad K N

[Bonnie Cresswe2]

It will be similar to the before business rule script, except that you have to find the records you want to update first.

The script below assumes that:
1. You have created a schedule with the weekends etc excluded
2. That schedule has a specific timezone linked to it
3. You have created a property called my.weekday.schedule.id that contains the sys_id of that schedule
4. You want to calculate the duration between the opened_at datetime and the sys_updated datetime on incident records 
5. You have created a custom Duration field on the incident table called u_my_weekday_duration to store the result
6. The script will be run from a scheduled job so you don't have access to a "current" record
7. For testing purposes you only want to update incident number INC0000003

/*
First do a GlideRecord query to find the record(s) that you want to update
Put whatever table and query conditions you want into the GlideRecord query to find the records that you want to update, the example below finds just one specific incident number for testing purposes
*/
calculateWeekdayIncidentDuration();

function calculateWeekdayIncidentDuration() {

    var incidentGR = new GlideRecord('incident');
    incidentGR.addQuery('number', 'INC0000003');
    incidentGR.query();
    while (incidentGR.next()) {
        // get the start and end dates to use in the duration calculation
        var startDate = incidentGR.opened_at;
        var endDate = incidentGR.sys_updated_on;

        //now call a function to use a schedule to calculate and return the business duration, sending the start and end dates
        var weekdayDur = calcDurationSchedule(startDate, endDate);

        // now update the duration field on the current record
        incidentGR.u_my_weekday_duration = weekdayDur;
        incidentGR.update();

    }
}

function calcDurationSchedule(_start, _end) {
    var weekdaySchedule = gs.getProperty('my.weekday.schedule.id', ''); // this property contains the sys_id of the schedule

    // get the schedule so we can get the timezone
    var weekdayScheduleTZ = new GlideRecord('cmn_schedule');
    weekdayScheduleTZ.get(weekdaySchedule);

    // Create schedule - pass in the sys_id of the schedule, and the schedule's timezone
    var sched = new GlideSchedule(weekdaySchedule, weekdaySchedule.time_zone);

    // Get duration based on schedule/timezone
    return (sched.duration(_start.getGlideObject(), _end.getGlideObject()));
}

When testing this out on my development instance I'm getting a big difference if calculating based on 24 x 7, than if calculating based on a weekday schedule (i.e. weekends and nights excluded).

Try that out and mark if correct if this answers your question 🙂

[shivaprasad3]

Hi Bonnie,

Thanks for the response.

I have created the property and given scheduled sysID to its value.

I have tried the above steps in my scheduled job but the function is not working ie "calcDurationSchedule" for my variables and its working as expected for the variables as you have mentioned ie opened_at and sys_updated_on.

I am dam sure that both variables which am passing to the functions are in DateTime format only still the sunction is not passsing any value for below:

var weekdayDur = calcDurationSchedule(a1, b1);

where a1 is:

var a1 = new GlideDateTime();

where b1 is 

var b1 = a.end_date;

and I have tried with passing the opened_at and sys_updated on for the same function and i am receiving difference like "1970-01-28 18:00:00" though there is no big difference in between those to dates.

I want to get the difference between current date and change requests end date as i have mentioned in the above script.

Any help much appreciated!!

Thanks and Regards,

Shivaprasad KN

[Bonnie Cresswe2]

Hi

Can you put your whole script in here?  It's a bit hard to see what might be missing/wrong.

Kind regards

Bonnie

[shivaprasad3]

Hi Bonnie,

Please find my below scheduled job's script:

==================Start======================

function calcDurationSchedule(_start, _end) {
var weekdaySchedule = gs.getProperty('my.weekday.schedule.id', '');
// Create schedule - pass in the sys_id of the schedule and the user's timezone
var weekdayScheduleTZ = new GlideRecord('cmn_schedule');
weekdayScheduleTZ.get(weekdaySchedule);

var sched = new GlideSchedule(weekdaySchedule, weekdaySchedule.time_zone);


return (sched.duration(_start.getGlideObject(), _end.getGlideObject()));

}
var a = new GlideRecord('change_request');
a.addQuery('state','-3');
a.query();
if(a.next())

{


var a1 = new GlideDateTime();//current date


var b1 = a.end_date;// end date

var weekdayDur = calcDurationSchedule(a1, b1);

gs.log("testttt "+weekdayDur); // not getting the log 

}

=====================End===================

Property which I have created:

Where the value is the sys id of the schedule ie "8-5 weekdays excluding holidays"

Please let me know if i need to do any corrections in the above.

Thanks and Regards,

Shivaprasad KN