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 Bonnie,

Did you get the chance to review above script?

Thanks in advance!

Thanks and Regards,

Shivaprasad KN

[shivaprasad3]

Hi Bonnie,

Have you get a chance to review the below script.

It would be helpful if assist me on this.

Thanks and Regards,

Shivaprasad K N

[shivaprasad3]

Hi Bonnie,

Please find the attached scripts below and let me know whats wrong in the script.

[Bonnie Cresswe2]

Hi - sorry have had a very hectic couple of weeks, will try to find some time later today to look at it

[Bonnie Cresswe2]

Hi

The start and end dates need to be GlideDateTime objects, which is why when using fields you need to include .getGlideObject(). However by using GlideDateTime() to define a1 you already have it as a GlideDateTime object.  So, you can simply drop .getGlideObject() from _start.

See below, this is your script with that removed, the log now appears.

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, _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 

}