Find date difference Excluding Holydays

  • 13 September 2018
  • 1 reply
  • 3 views

Badge +4

Hi,

 I have two date fields StartDate and ExpectedEndDate. Also one list PublicHolydays which will contain all public holyday dates. I need to exclude public holydays and weekend(Friday and saturday) when i calculate expected end date. There is one field KPI integer field . The process is like I will select the start date for item and the item have one KPI column in it (eg:2). I have to calculate the expected end date excluding weekend and holyday.

eg:

1)Item 1 and its KPI =2.

2)I select start date 13/09/2018.(Thursday).

3)16/09/2019 is a public holyday . it is stored in the public holyday List

4)The resulted expected end date should be 18/09/2018 since the KPI is 2 and should avoid weekend and public holyday.

Jquery or nintex formula 


1 reply

Badge +4

// JavaScript source code

var HOLIDAYS = [];

function HolyDaysList() {
$().SPServices({
operation: "GetListItems",
async: false,
si: "http://devappsrv2/EN/HelpDesk",
listName: "Public holydays",
CAMLQuery: "<Query></Query>",
CAMLViewFields: "<ViewFields Properties='True'><FieldRef Name='Date' /> </ViewFields>",
CAMLQueryOptions: "<QueryOptions></QueryOptions>",
completefunc: function (xData, Status) {
if ($(xData.responseXML).SPFilterNode("z:row").length > 0) {
$(xData.responseXML).SPFilterNode("z:row").each(function () {
debugger;
var phday = new Date($(this).attr("ows_Date")).toISOString().slice(0, 10);
HOLIDAYS.push(phday);
});
}
}
});
}
NWF$(document).ready(function () {
debugger;
HolyDaysList();
var startDate = NWF$('.cssStartDate').find('input').val();
var dtSplit = startDate.split('/');
startDate = new Date(dtSplit[2], parseInt(dtSplit[1]) - 1, dtSplit[0]);
startDate.setDate(startDate.getDate() + 0);
var kpi = NWF$('.cssKPI').find('input').val();

var result = new Date();
var endDate = result.setDate(result.getDate() + parseInt(kpi) - parseInt(1));
var expEndDate = calcBusinessDays(startDate, endDate, kpi);
});

NWF$("#" + dtStartDate).change(function () {

var startDate = NWF$('.cssStartDate').find('input').val();
var dtSplit = startDate.split('/');
startDate = new Date(dtSplit[2], parseInt(dtSplit[1]) - 1, dtSplit[0]);
startDate.setDate(startDate.getDate() + 0);
var kpi = NWF$('.cssKPI').find('input').val();
var result = new Date();
result = startDate;
var endDate = result.setDate(result.getDate() + parseInt(kpi))
var startDateAct = NWF$('.cssStartDate').find('input').val();
var dtSplitAct = startDateAct.split('/');
startDateAct = new Date(dtSplitAct[2], parseInt(dtSplitAct[1]) - 1, dtSplitAct[0]);
startDateAct.setDate(startDateAct.getDate() + 0);
var expEndDate = calcBusinessDays(startDateAct, endDate, kpi);
});

function calcBusinessDays(d1, d2, kpi) {
if (NWF$.inArray(d1.toISOString().slice(0, 10), HOLIDAYS) > -1)
{
alert('You can not select public holiday as start date!!');
NWF$('.cssStartDate').find('input').val('');
NWF$('.cssExpEndDate').find('input').val('');
return;
}
var diff = kpi;
var work = diff;
var d = d1;
while (d <= d2) {
d = new Date(d);
if ((d.getDay() == 5) || (d.getDay() == 6)) {
work++;
new Date(d2).setDate(new Date(d2).getDate() + 1);
} else if (NWF$.inArray(d.toISOString().slice(0, 10), HOLIDAYS) > -1) {
work++;
new Date(d2).setDate(new Date(d2).getDate() + 1);
}
d.setDate(d.getDate() + 1);
}

kpi = work;
d1 = new Date(d1);
var expEndDate = d1.setDate(d1.getDate() + parseInt(kpi)-parseInt(1));
var exp = NWF$('.cssExpEndDate').find('input');
var a = new Date(expEndDate);
exp.datepicker('setDate', a);
}

Reply