台指結算日規則相較於摩台結算日規則簡單。
- 每月的第三週星期三。
- 年節封關提早、農曆年節提早、7月~8月颱風延後,需手動調整。
function CreateTXFSettlementdate() { var nCount = 0; var nGap = 15; var Datelist = new Array(2 * nGap +1); var dBasedate, dCurrentdate, dPastdate; dPastdate = DateAdd("m", (-1) * nGap, new Date()); for(var i = 0 ; i <= 2 * nGap + 1 ; i++) { dBasedate = DateAdd("m", i, new Date(dPastdate.getYear(), dPastdate.getMonth(), 1)); for(var j = 14 ; j <= 24 ; j++) { dCurrentdate = new Date(dBasedate.getYear(), dBasedate.getMonth(), j); if(checkTXFDayofWeek(dCurrentdate)) { Datelist[nCount] = dCurrentdate.getFullYear() + "/" + padLeft(parseInt(dCurrentdate.getMonth()+1), 2) + "/" + padLeft(dCurrentdate.getDate(), 2); nCount++; } } } /* 清除"結算日"工作表內容 */ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("台指結算日"); var Row = getLastRowOfColumn("台指結算日", "A:A"); if (Row == 1) Row++; ss.getSheetByName("台指結算日").getRange("A2:A" + Row).clear(); Row = getLastRowOfColumn("台指結算日", "B:B"); for(var i = 0 ; i < nCount ; i++) { for(var j = 2 ; j < Row ; j++) { if(Datelist[i].substring(0, 8) == sheet.getRange(j, 2).getValue().substring(0, 8)) { Datelist[i] = sheet.getRange(j, 2).getValue(); } } sheet.getRange(i + 2, 1).setValue("'" + Datelist[i]); } } /* 確認日期為每月第三週星期三 */ function checkTXFDayofWeek(date) { if(isNaN(date)) return 0; var weekofMonth = DatePart("wm", date); if(DatePart("w", new Date(date.getYear(), date.getMonth(), 1)) > 3) weekofMonth = weekofMonth - 1; return ((DatePart("w", date) == 3) && (weekofMonth == 3)) ? true : false; } /* 取得資料欄的最後一個位置 */ function getLastRowOfColumn(sheetName, sheetRange) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var Rowdata = ss.getSheetByName(sheetName).getRange(sheetRange).getValues(); var row = 0; while ( Rowdata[row].join("") ) row++; //while ( Rowdata[row][0] != "" ) row++; return row; } /* 補0 */ function padLeft(str, len) { str = '' + str; return (str.length >= len) ? str : padLeft("0" + str, len); } /* 取得日期資訊 */ function DatePart(interval, date) { switch (interval.toLowerCase()) { case "q": dayIndex = Math.floor((date.getMonth() + 1) / 3) + ((date.getMonth() + 1) % 3 ? 1 : 0); break; case "wm": dayIndex = date.getWeekOfMonth(); break; case "w": dayIndex = date.getDay(); break; case "h": dayIndex = date.getHours(); break; case "m": dayIndex = date.getMinutes(); break; case "s": dayIndex = date.getSeconds(); break; } return dayIndex } /* 日期時間加減 */ function DateAdd(interval, number, date) { switch (interval.toLowerCase()) { case "y": return new Date(date.setFullYear(date.getFullYear() + number)); case "m": return new Date(date.setMonth(date.getMonth() + number)); case "d": return new Date(date.setDate(date.getDate() + number)); case "w": return new Date(date.setDate(date.getDate() + 7 * number)); case "h": return new Date(date.setHours(date.getHours() + number)); case "n": return new Date(date.setMinutes(date.getMinutes() + number)); case "s": return new Date(date.setSeconds(date.getSeconds() + number)); case "l": return new Date(date.setMilliseconds(date.getMilliseconds() + number)); } } /* 計算當前日期在本月份的周數 */ Date.prototype.getWeekOfMonth = function(weekStart) { weekStart = (weekStart || 0) - 0; if(isNaN(weekStart) || weekStart > 6) weekStart = 0; var dayOfWeek = this.getDay(); var day = this.getDate(); return Math.ceil((day - dayOfWeek - 1) / 7) + ((dayOfWeek >= weekStart) ? 1 : 0); }