Excluding holidays in business-day date calculation

rah dev · 3 weeks ago

Hi Community, good day

I’m using an onChange client script that calculates two future dates (35 business days and 90 business days) based on a selected date, and it already excludes weekends. Now I need to update this script so it also excludes US holidays during the calculation. Can someone guide me on how to properly modify or improve the script to handle holiday exclusion as well?

I am using this

function onChange(control, oldValue, newValue) {
if (!newValue) return;

var base = new Date(newValue);
if (isNaN(base.getTime())) return;

function calc(d, x) {
var r = new Date(d);
var c = 0;
while (c < x) {
r.setDate(r.getDate() + 1);
var k = r.getDay();
if (k != 0 && k != 6) c++;
}
return r.toISOString().split('T')[0];
}

var dA = calc(base, 35);
var dB = calc(base, 90);

g_form.setValue('dat2_35', dA);
g_form.setValue('dat2_90', dB);
}

thanks in Advance

k_lutz · 3 weeks ago

Hi rah dev,

You would need to make a ajax call to get your holidays (most flexible and least maintenance way). I took your script above and adjusted how it might be done. It is not tested and uses a fake schedule...so you need to have a holiday schedule that can be used and that you know if working as desired but this may help get you started:

Modified client script:

function onChange(control, oldValue, newValue) {

if (!newValue) return;

var base = new Date(newValue);

if (isNaN(base.getTime())) return;

// Get holidays from server

var ga = new GlideAjax('HolidayUtils');

ga.addParam('sysparm_name', 'getHolidays');

ga.addParam('sysparm_year', base.getFullYear());

ga.getXMLAnswer(function(response) {

var holidays = JSON.parse(response);

calculateDates(base, holidays);

});

function calculateDates(baseDate, holidays) {

function isHoliday(date) {

var dateStr = date.toISOString().split('T')[0];

return holidays.indexOf(dateStr) !== -1;

}

function calc(d, x) {

var r = new Date(d);

var c = 0;

while (c < x) {

r.setDate(r.getDate() + 1);

var k = r.getDay();

if (k != 0 && k != 6 && !isHoliday(r)) {

c++;

}

return r.toISOString().split('T')[0];

}

var dA = calc(baseDate, 35);

var dB = calc(baseDate, 90);

g_form.setValue('dat2_35', dA);

g_form.setValue('dat2_90', dB);

}

Fake schedule called “Holiday” that has the dates your company follows to be called in the script include.

var HolidayUtils = Class.create();

HolidayUtils.prototype = Object.extendsObject(AbstractAjaxProcessor, {

getHolidays: function() {

var year = this.getParameter('sysparm_year');

var holidays = [];

// Query cmn_schedule_span table for holidays

var gr = new GlideRecord('cmn_schedule_span');

gr.addQuery('type', 'exclude'); // Holiday exclusions

gr.addQuery('name', 'CONTAINS', 'Holiday');

gr.query();

while (gr.next()) {

var startDate = gr.start_date_time.getDisplayValue().split(' ')[0];

if (startDate.indexOf(year) === 0) {

holidays.push(startDate);

}

return JSON.stringify(holidays);

},

type: 'HolidayUtils'

});

Bert_c1 · 3 weeks ago

Here's an example using the '8 - 5 workdays excluding holidays' schedule.

Client script code:

function onChange(control, oldValue, newValue, isLoading, isTemplate) {
	if (isLoading || newValue === '') {
		return;
	}
	if (oldValue == newValue) {
		return;
	}

	var startDate = g_form.getValue('u_start_date');
  	var ga = new GlideAjax('getFutureScheduleDate');
	ga.addParam('sysparm_name', 'getEndDate');
	ga.addParam('sysparm_start_date', startDate);
	ga.addParam('sysparm_days_to_add', '35');
	ga.getXMLAnswer(getResponse1);

	var ga = new GlideAjax('getFutureScheduleDate');
	ga.addParam('sysparm_name', 'getEndDate');
	ga.addParam('sysparm_start_date', startDate);
	ga.addParam('sysparm_days_to_add', '90');
	ga.getXMLAnswer(getResponse2);

	// callback function for returning the result from the script include
	function getResponse1(response) {
		var dueDate = response;
		alert('First Due Date: ' + dueDate);
		if (dueDate == "Invalid value!")
			alert('The date entered is not in the schedule');
		else
			g_form.setValue('u_date_35', dueDate);
	}

	// callback function for returning the result from the script include
	function getResponse2(response) {
		var dueDate = response;
		alert('Second Due Date: ' + dueDate);
		if (dueDate == "Invalid value!")
			alert('The date entered is not in the schedule');
		else
			g_form.setValue('u_date_90', dueDate);
	}
}

change column names to match.

the script include:

var getFutureScheduleDate = Class.create();
getFutureScheduleDate.prototype = Object.extendsObject(AbstractAjaxProcessor, {

	getEndDate : function() {
		// get number of days to add
		var startDate = this.getParameter("sysparm_start_date");
		var daysToAddValue = this.getParameter("sysparm_days_to_add");
		var daysToAdd = parseInt(daysToAddValue);

		var newDate = new GlideDateTime(startDate);
		var tzoms = newDate.getTZOffset();
		var tzos = -1 * (tzoms/1000);
		newDate.addSeconds(tzos);			// adjust for TZ offset

		gs.info('getEndDate: startDate = ' + startDate + ', starting newDate = ' + newDate.getDisplayValue());
		// set schedule: 8 - 5 weekdays excluding holidays
		var sched = new GlideSchedule('090eecae0a0a0b260077e1dfa71da828');

		// check if starting date is in schedule, needed since we add a day, to avoid infinite loop
		var nDate = newDate.getDisplayValue();
		newDate.setDisplayValue(nDate);
		if (sched.isInSchedule(new GlideDateTime(newDate)))
			gs.info('getEndDate: newDate: ' + newDate.getDisplayValue() + ' is in schedule');
		else {
			gs.info('getEndDate: newDate: ' + newDate.getDisplayValue() + ' is NOT in schedule');
			return 'Invalid value!';
		}

		// Add days
		newDate.addDaysLocalTime(1);
		var dayCount = 1;
		var loopCount = 0;   // extra guard to prevent infinite loop

		// Find date in the future
		while ((dayCount < daysToAdd) && (loopCount < (2*daysToAdd))) {
			if (sched.isInSchedule(newDate)) {
//				gs.info('getEndDate: loopCount = ' + loopCount + ', Date: ' + newDate.getDisplayValue() + ', is in schedule');
				dayCount++;
			}
			else {
	//			gs.info('getEndDate: loopCount = ' + loopCount + ', Date: ' + newDate.getDisplayValue() + ', is NOT in schedule');
			}
			// Now add one day and build date/time value to check next
			newDate.addDaysLocalTime(1);
			loopCount++;
		}
		gs.info("getEndDate: daysToAdd: " + daysToAdd + ", dayCount = " + dayCount + ", loopCount = " + loopCount);
		gs.info("getEndDate: date: " + startDate + ", returning newDate = " + newDate.getDisplayValue());
		return newDate.getDisplayValue();
	},

    type: 'getFutureScheduleDate'
});