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

[Bonnie Cresswe2]

Hi

You can use a schedule in the duration calculation, so the first step would be to create a schedule and configure it to only be open on weekdays.

Get the sys_id for that schedule (and store it in a property that is accessible to your admins - in case you ever need to change the schedule you want to use without having to change your script, i.e. you will need this sys_id but you don't want to hardcode it into your script).

So let's say you want the difference between the current incident record's opened date and last updated date, and you are storing the sys_id of your schedule in a property called my.weekday.schedule.id and you wanted to store the resulting duration in a field called u_my_weekday_duration.  You could try this in a before update business rule:

// get the start and end dates for the duration
var startDate = current.opened_at;
var endDate = current.sys_updated_on;

// get the user so we can grab a timezone
var currentUser = new GlideRecord('sys_user');
currentUser.get(gs.getUserID());

//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
current.u_my_weekday_duration = weekdayDur;

 

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 sched = new GlideSchedule(weekdaySchedule, currentUser.time_zone);

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

If you need to do it in a scheduled job you can just modify the above to include a GlideRecord like you have done in the script in your question, instead of using current like I have in my example.

Let me know how you go, and mark as correct if it works for you 🙂

[Inactive_Use407]

Hi Bonnie, how you doing?

Can you help me in something close to this question?

I need compare two dates - thats ok, its working

But I wanna show in this field, only number of days, without hh:mm:ss.

bellow my script:

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

	var gdt2 = new GlideDateTime(current.valid_to.getDisplayValue());
	var gdt1 = new GlideDateTime(gs.nowDateTime());
	var dur = GlideDateTime.subtract(gdt1, gdt2);

	var dur1= new GlideDuration();
	dur1.setValue(dur.getValue());

	current.u_days_to_expire = dur1.getDurationValue();

	
})(current, previous);

[farci]

Hi,

You can convert the result to days by dividing it, Glide Duration when exported gets you the results in seconds. You can have the new field to divide the duration by 86400 number of seconds in a day.

Regards,
Narmi

[Inactive_Use407]

thanks a lot!!!