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,

Thanks a lot for your kind help on this.

Unfortunately I am receiving bad values in log ie "testttt 1970-01-01 00:00:00" 

Please let me know if you know the reason for this dummy values.

Thanks and Regards,

Shivaprasad K N

[Bonnie Cresswe2]

Hi 

The value looks valid, it's probably just that your start date is after your end date, so is coming up as zero duration (and a duration cannot store a negative as far as I know(?))

See this documentation for info on how duration fields are returned in script:

https://docs.servicenow.com/bundle/madrid-application-development/page/script/general-scripting/refe...

Play around with the data that is in the end_date field and you will see it returns a value if your end_date is after the start datetime (in your case the current datetime).

Regards

Bonnie