Goal: I'm trying to do date calculations by adding 1 to 3 days depending on what day the user edits the cell. The following are the main conditions that wish to have.
- If it's between 2:00 PM and 6:30 PM (JST) on a Monday through Thursday, then add 1 day to the current date.
- If it's between 2:00 PM and 6:30 PM (JST) on a Friday, then add 3 days to the current date to get it on a Monday and avoid getting weekend dates.
- If Monday or any other week day is not a working day based on Japan's bank holiday system, I want to add as many days necessary until we can get the next working day. For example, the 11th of January, 2021 is a Monday, however for Japan, it's a bank holiday, so in this scenario, I would like to get the 12th of January.
Issue I don't know how or if it's even possible to somehow automatically add dates and always get a working day that's based on Japan's bank holiday system.
Current Code This is the code that I have at the moment, which I kindly received from @NaziA and it works great as it exclude weekends. But I'm just wondering if we can take it a step further by also excluding Japan's bank holidays and only get Japan's working days. Continuation to this question.
function onEdit(e) {
const sheetName = 'Status';
const statusSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const cell = statusSheet.getActiveCell();
const row = cell.getRow();
const column = cell.getColumn();
const editedRangeValue = cell.getValue();
// var currentDate = new Date(new Date().toLocaleString('en-US', { timeZone: 'Japan' }));
// The above line of code translates your time into japan timezone but since
// sheets is converting your timezone automatically to the regional one,
// it adjusts an additional day when used. Be careful in using the above line.
var currentDate = new Date();
// Use the line below to test different time slots, we do not want to wait the
// exact time just to test this script.
// currentDate.setHours(11, 30, 0 ,0);
startDate = new Date(currentDate.getTime());
startDate.setHours(9, 30, 0, 0);
endDate1 = new Date(currentDate.getTime());
endDate1.setHours(14, 0, 0, 0);
endDate2 = new Date(currentDate.getTime());
endDate2.setHours(18, 30, 0, 0);
// Column numbers for Status sheet
const caseTypeColumnStatus = 4;
if (sheetName == 'Status' && column == caseTypeColumnStatus && row > 15 && editedRangeValue == 'Chat => Email') {
var statusColumn = column + 3;
if (startDate <= currentDate && endDate1 > currentDate) { // If the current time is between 9:30 A.M. and 2:00 P.M. (JST), then TRUE.
// Insert today's date
statusSheet.getRange(row, statusColumn).setValue(currentDate);
} else if (endDate1 <= currentDate && endDate2 > currentDate) { // If the current time is between 2:00 P.M. and 6:30 P.M. (JST), then TRUE.
// Insert tomorrow's date
var tomorrow = new Date(currentDate.setDate(currentDate.getDate() + 1));
if(tomorrow.getDay() == 6){
// if saturday, add 2 days
tomorrow.setDate(tomorrow.getDate() + 2);
}
else if(tomorrow.getDay() == 0){
// if sunday, add 1 day
tomorrow.setDate(tomorrow.getDate() + 1);
}
statusSheet.getRange(row, statusColumn).setValue(tomorrow);
}
}
}