After revisiting this script, and some modifications, the following is available to allow a user to add a feature that calculates the expected delivery date.
// array of ISO YYYY-MM-DD format dates
publicHolidays = {
uk:["2020-01-01","2020-04-10","2020-04-13","2020-05-08","2020-05-25",
"2020-08-03","2020-08-31","2020-12-25","2020-12-28"],
usa:["2020-01-01","2020-01-20","2020-02-14","2020-02-17","2020-04-10",
"2020-04-12","2020-05-10","2020-05-25","2020-06-21","2020-07-03",
"2020-07-04","2020-09-07","2020-10-12","2020-10-31","2020,11,11",
"2020-11-26","2020-12-25"]
}
// check if there is a match in the array
Date.prototype.isPublicHoliday = function( data ){// we check for a public holiday
if(!data) return 1;
return data.indexOf(this.toISOString().slice(0,10))>-1? 0:1;
}
// calculation of business days
Date.prototype.businessDays = function( d, holidays ){
var holidays = holidays || false, t = new Date( this ); // copy date.
while( d ){ // we loop while d is not zero...
t.setDate( t.getDate() + 1 ); // set a date and test it
switch( t.getDay() ){ // switch is used to allow easier addition of other days of the week
case 0: case 6: break;// sunday & saturday
default: // check if we are a public holiday or not
d -= t.isPublicHoliday( holidays );
}
}
return t.toISOString().slice(0,10); // just the YYY-MM-DD
}
// dummy var, could be a form field input
OrderDate = "2020-02-12";
// test with a UK holiday date
var deliveryDate = new Date(OrderDate).businessDays(7, publicHolidays.usa);
// expected output 2020-02-25
console.log("Order date: %s, Delivery date: %s",OrderDate,deliveryDate );
Order date: 2020-02-12, Delivery date: 2020-02-25
The prototype is written to allow inputs from forms (HTML5 forms) of date type inputs as they are already in an ISO YYYY-MM-DD format and the output is formatted as such should that be needing to update a particular field.
The typical use would be...
var delDate = new Date( ISOdate ).businessDays( addBusinessDays, holidayData );
where the delDate is an ISO format date, eg, 2020-01-01
Here is an option using a WorkSchedule table, which will contain the business hours that are available to count towards the SLA. To account for weekends and holidays, just do not insert records for these days into the WorkSchedule table. This solution also uses a "Tally" table aka numbers table in the due date calc.
Essentially, if there are any days of un-taken leave at the balance sheet date and these are going to be paid within 12 months of this date, an accrual needs to be put in place. How to Calculate the Holiday Pay Accrual The holiday pay accrual should be calculated based on each employee’s gross salary.
--- If @date is before the first business day, we'll --- use startBusinessDay, otherwise @date is fine. SELECT @businessDay= (CASE WHEN @date<startBusinessDay THEN startBusinessDay ELSE @date END) FROM weeks WHERE lastHoliday<=@date AND @date<=endBusinessDay; RETURN @businessDay; END;
When calculating business or working days, the focal point is the weekend which is not the same everywhere in the world. Your weekend may be made up by some other two days or a single day in the week. No matter, you can make NETWORKDAYS.INTL accept your weekend.
I've adapted Mark Giblin's revised code to better deal with end of year dates and also U.S. federal holidays. See below...
function businessDaysFromDate(date,businessDays) {
var counter = 0, tmp = new Date(date);
while( businessDays>=0 ) {
tmp.setTime( date.getTime() + counter * 86400000 );
if(isBusinessDay (tmp)) {
--businessDays;
}
++counter;
}
return tmp;
}
function isBusinessDay (date) {
var dayOfWeek = date.getDay();
if(dayOfWeek === 0 || dayOfWeek === 6) {
// Weekend
return false;
}
holidays = [
'12/31+5', // New Year's Day on a saturday celebrated on previous friday
'1/1', // New Year's Day
'1/2+1', // New Year's Day on a sunday celebrated on next monday
'1-3/1', // Birthday of Martin Luther King, third Monday in January
'2-3/1', // Washington's Birthday, third Monday in February
'5~1/1', // Memorial Day, last Monday in May
'7/3+5', // Independence Day
'7/4', // Independence Day
'7/5+1', // Independence Day
'9-1/1', // Labor Day, first Monday in September
'10-2/1', // Columbus Day, second Monday in October
'11/10+5', // Veterans Day
'11/11', // Veterans Day
'11/12+1', // Veterans Day
'11-4/4', // Thanksgiving Day, fourth Thursday in November
'12/24+5', // Christmas Day
'12/25', // Christmas Day
'12/26+1', // Christmas Day
];
var dayOfMonth = date.getDate(),
month = date.getMonth() + 1,
monthDay = month + '/' + dayOfMonth;
if(holidays.indexOf(monthDay)>-1){
return false;
}
var monthDayDay = monthDay + '+' + dayOfWeek;
if(holidays.indexOf(monthDayDay)>-1){
return false;
}
var weekOfMonth = Math.floor((dayOfMonth - 1) / 7) + 1,
monthWeekDay = month + '-' + weekOfMonth + '/' + dayOfWeek;
if(holidays.indexOf(monthWeekDay)>-1){
return false;
}
var lastDayOfMonth = new Date(date);
lastDayOfMonth.setMonth(lastDayOfMonth.getMonth() + 1);
lastDayOfMonth.setDate(0);
var negWeekOfMonth = Math.floor((lastDayOfMonth.getDate() - dayOfMonth - 1) / 7) + 1,
monthNegWeekDay = month + '~' + negWeekOfMonth + '/' + dayOfWeek;
if(holidays.indexOf(monthNegWeekDay)>-1){
return false;
}
return true;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With