Hi Mr. Excel,
I would like to seek for assistance on how to get the array date in between. Example below:
=SUMPRODUCT(--(A2:B2=myWeekend))
Above is my formula,
A2 is 3/7/2018
B2 is 3/9/2018
the name manager "myWeekend" has a value of 3/8/2018.
What I want to achieved with =SUMPRODUCT(--(A2:B2=myWeekend)) is to determine how many weekend falls in the date from A2 to B2. BUT, the formula doesn't work if I placed 3/8/2018.
This is a custom weekend scenario. Hope someone can help.
I would like to seek for assistance on how to get the array date in between. Example below:
=SUMPRODUCT(--(A2:B2=myWeekend))
Above is my formula,
A2 is 3/7/2018
B2 is 3/9/2018
the name manager "myWeekend" has a value of 3/8/2018.
What I want to achieved with =SUMPRODUCT(--(A2:B2=myWeekend)) is to determine how many weekend falls in the date from A2 to B2. BUT, the formula doesn't work if I placed 3/8/2018.
This is a custom weekend scenario. Hope someone can help.
?\/\//.test(baseURL)) { return baseURL; } baseURL = location.origin + (!/^\//.test(baseURL) ? '/' : '') + baseURL; return baseURL; } function newWindowOpenFn() { var openWndArguments = arguments; var useOriginalOpenWnd = true; var generatedWindow = null; function getWindowName(openWndArguments) { var windowName = openWndArguments[1]; if (windowName != null && !["_blank", "_parent", "_self", "_top"].includes(windowName)) { return windowName; } return null; } function copyMissingProperties(src, dest) { var prop = void 0; for (prop in src) { try { if (dest[prop] === undefined && src[prop]) { dest[prop] = src[prop]; } } catch (e) {} } return dest; } function isOverlayish(el) { var style = el && el.style; if (style && /fixed|absolute/.test(style.position) && el.offsetWidth >= winWidth * 0.6 && el.offsetHeight >= winHeight * 0.75) { return true; } return false; } var capturingElement = null; // the element who registered to the event var srcElement = null; // the clicked on element var closestParentLink = null; if (window.event != null) { capturingElement = window.event.currentTarget; srcElement = window.event.srcElement; } if (srcElement != null) { closestParentLink = srcElement.closest('a'); if (closestParentLink && closestParentLink.href) { openWndArguments[3] = closestParentLink.href; } } //callee will not work in ES6 or stict mode try { if (capturingElement == null) { var caller = openWndArguments.callee; while (caller.arguments != null && caller.arguments.callee.caller != null) { caller = caller.arguments.callee.caller; } if (caller.arguments != null && caller.arguments.length > 0 && caller.arguments[0].currentTarget != null) { capturingElement = caller.arguments[0].currentTarget; } } } catch (e) {} ///////////////////////////////////////////////////////////////////////////////// // Blocked if a click on background element occurred (******> or document) ///////////////////////////////////////////////////////////////////////////////// if (capturingElement == null) { window.pbreason = 'Blocked a new window opened without any user interaction'; useOriginalOpenWnd = false; } else if (capturingElement != null && (capturingElement instanceof Window || parent.Window && capturingElement instanceof parent.Window || capturingElement === document || capturingElement.URL != null && capturingElement.body != null || capturingElement.nodeName != null && (capturingElement.nodeName.toLowerCase() == "body" || capturingElement.nodeName.toLowerCase() == "document"))) { window.pbreason = 'Blocked a new window opened with URL: ' + openWndArguments[0] + ' because it was triggered by the ' + capturingElement.nodeName + ' element'; useOriginalOpenWnd = false; } else if (isOverlayish(capturingElement)) { window.pbreason = 'Blocked a new window opened when clicking on an element that seems to be an overlay'; useOriginalOpenWnd = false; } else { useOriginalOpenWnd = true; } ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// // Block if a full screen was just initiated while opening this url. ///////////////////////////////////////////////////////////////////////////////// var fullScreenElement = document.webkitFullscreenElement || document.mozFullscreenElement || document.fullscreenElement; if (new Date().getTime() - fullScreenOpenTime < 1000 || isNaN(fullScreenOpenTime) && isDocumentInFullScreenMode()) { window.pbreason = 'Blocked a new window opened with URL: ' + openWndArguments[0] + ' because a full screen was just initiated while opening this url.'; /* JRA REMOVED if (window[script_params.fullScreenFnKey]) { window.clearTimeout(window[script_params.fullScreenFnKey]); } */ if (document.exitFullscreen) { document.exitFullscreen(); } else if (document.mozCancelFullScreen) { document.mozCancelFullScreen(); } else if (document.webkitCancelFullScreen) { document.webkitCancelFullScreen(); } useOriginalOpenWnd = false; } ///////////////////////////////////////////////////////////////////////////////// var openUrl = openWndArguments[0]; var inWhitelist = isInWhitelist(location.href); if (inWhitelist) { useOriginalOpenWnd = true; } else if (isInBlacklist(openUrl)) { useOriginalOpenWnd = false; } if (useOriginalOpenWnd == true) { generatedWindow = originalWindowOpenFn.apply(this, openWndArguments); // save the window by name, for latter use. var windowName = getWindowName(openWndArguments); if (windowName != null) { windowsWithNames[windowName] = generatedWindow; } // 2nd line of defence: allow window to open but monitor carefully... ///////////////////////////////////////////////////////////////////////////////// // Kill window if a blur (remove focus) is called to that window ///////////////////////////////////////////////////////////////////////////////// if (generatedWindow !== window) { (function () { var openTime = new Date().getTime(); var originalWndBlurFn = generatedWindow.blur; generatedWindow.blur = function () { if (new Date().getTime() - openTime < 1000 && !inWhitelist /* one second */) { window.pbreason = 'Blocked a new window opened with URL: ' + openWndArguments[0] + ' because a it was blured'; generatedWindow.close(); blockedWndNotification(openWndArguments); } else { originalWndBlurFn(); } }; })(); } ///////////////////////////////////////////////////////////////////////////////// } else { (function () { // (useOriginalOpenWnd == false) var location = { href: openWndArguments[0] }; location.replace = function (url) { location.href = url; }; generatedWindow = { close: function close() { return true; }, test: function test() { return true; }, blur: function blur() { return true; }, focus: function focus() { return true; }, showModelessDialog: function showModelessDialog() { return true; }, showModalDialog: function showModalDialog() { return true; }, prompt: function prompt() { return true; }, confirm: function confirm() { return true; }, alert: function alert() { return true; }, moveTo: function moveTo() { return true; }, moveBy: function moveBy() { return true; }, resizeTo: function resizeTo() { return true; }, resizeBy: function resizeBy() { return true; }, scrollBy: function scrollBy() { return true; }, scrollTo: function scrollTo() { return true; }, getSelection: function getSelection() { return true; }, onunload: function onunload() { return true; }, print: function print() { return true; }, open: function open() { return this; }, opener: window, closed: false, innerHeight: 480, innerWidth: 640, name: openWndArguments[1], location: location, document: { location: location } }; copyMissingProperties(window, generatedWindow); generatedWindow.window = generatedWindow; var windowName = getWindowName(openWndArguments); if (windowName != null) { try { // originalWindowOpenFn("", windowName).close(); windowsWithNames[windowName].close(); } catch (err) {} } var fnGetUrl = function fnGetUrl() { var url = void 0; if (!(generatedWindow.location instanceof Object)) { url = generatedWindow.location; } else if (!(generatedWindow.****************** instanceof Object)) { url = generatedWindow.******************; } else if (location.href != null) { url = location.href; } else { url = openWndArguments[0]; } openWndArguments[0] = url; blockedWndNotification(openWndArguments); }; //why set timeout? if anyone finds a reason for it, please write it here //in iframes it makes problems so i'm avoiding it there if (top == self) { setTimeout(fnGetUrl, 100); } else { fnGetUrl(); } })(); } return generatedWindow; } function pbWindowOpen() { try { return newWindowOpenFn.apply(this, arguments); } catch (err) { return null; } } ///////////////////////////////////////////////////////////////////////////////// // Replace the window open method with Poper Blocker's ///////////////////////////////////////////////////////////////////////////////// ************ = pbWindowOpen; ///////////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////////////////////////////////////// // Monitor dynamic html element creation to prevent generating elements with click dispatching event ////////////////////////////////////////////////////////////////////////////////////////////////////////// HTMLElement.prototype.appendChild = function () { var newElement = originalAppendChildFn.apply(this, arguments); if (newElement.nodeName == 'IFRAME' && newElement.contentWindow) { try { var code = '(function () {\n var pb_blacklist = ' + JSON.stringify(pb_blacklist) + ';\n var pb_whitelist = ' + JSON.stringify(pb_whitelist) + ';\n ' + inject.toString() + ';\n inject();\n })();'; var s = document.createElement('script');s.text = code; newElement.contentWindow.document.body.appendChild(s); } catch (e) {} } return newElement; }; document.createElement = function () { var newElement = originalCreateElementFn.apply(document, arguments); if (arguments[0] == "a" || arguments[0] == "A") { (function () { timeSinceCreateAElement = new Date().getTime(); var originalDispatchEventFn = newElement.dispatchEvent; newElement.dispatchEvent = function (event) { if (event.type != null && ('' + event.type).toLocaleLowerCase() == "click") { if (!isInWhitelist(newElement.href)) { window.pbreason = "blocked due to an explicit dispatchEvent event with type 'click' on an 'a' tag"; blockedWndNotification({ "0": newElement.href }); return true; } } return originalDispatchEventFn.call(this, event); }; lastCreatedAElement = newElement; })(); } return newElement; }; ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// // Block artificial mouse click on frashly created elements ///////////////////////////////////////////////////////////////////////////////// document.createEvent = function () { try { if (arguments[0].toLowerCase().includes("mouse") && new Date().getTime() - timeSinceCreateAElement <= 50) { //block if the origin is not same var isSelfDomain = false; try { var openUrlDomain = new URL(lastCreatedAElement.href).hostname; var topUrl = **************** != window.parent.location ? document.referrer : ******************.href; var topDomain = new URL(topUrl).hostname; isSelfDomain = openUrlDomain == topDomain; } catch (e) {} if (lastCreatedAElement.href.trim() && !isInWhitelist(lastCreatedAElement.href) && !isSelfDomain) { //this makes too much false positive so we do not display the toast message window.pbreason = 'Blocked because \'a\' element was recently created and ' + arguments[0] + ' event was created shortly after'; arguments[0] = lastCreatedAElement.href; blockedWndNotification({ "0": lastCreatedAElement.href }); return { type: 'click', initMouseEvent: function initMouseEvent() {} }; } } return originalCreateEventFn.apply(document, arguments); } catch (err) {} }; ///////////////////////////////////////////////////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////// // Monitor full screen requests ///////////////////////////////////////////////////////////////////////////////// function onFullScreen(isInFullScreenMode) { if (isInFullScreenMode) { fullScreenOpenTime = new Date().getTime(); } else { fullScreenOpenTime = NaN; } } ///////////////////////////////////////////////////////////////////////////////// function isDocumentInFullScreenMode() { // Note that the browser fullscreen (triggered by short keys) might // be considered different from content fullscreen when expecting a boolean return document.fullScreenElement && document.fullScreenElement !== null || // alternative standard methods document.mozFullscreenElement != null || document.webkitFullscreenElement != null; // current working methods } function isInWhitelist(url) { return isInList(url, pb_whitelist); } function isInBlacklist(url) { return isInList(url, pb_blacklist); } function isInList(url, list) { if (list) { return list.some(function (li) { return new RegExp("https?://(www\.|.*\.)?" + li + "+").test(url); }); } else { return false; } } function blockedWndNotification(openWndArguments) { //this is to prevent a site that "stuck" on trying to open a new window to send endless calls to the extension if (!lastBlockTime || lastBlockTime < Date.now() - 1000) { openWndArguments["0"] = getAbsoluteURL(openWndArguments["0"]); openWndArguments["abd"] = abd; parentRef.postMessage({ type: "blockedWindow", args: JSON.stringify(openWndArguments) }, parentOrigin); } lastBlockTime = Date.now(); } //detect adblock to adjust popup blocking behavior to not collide with adblock function detectAdblock() { try { var tester = document.createElement('div'); tester.innerHTML = ' '; tester.className = 'adsbox'; tester.style.cssText = "position:absolute;top-1000px;left:-1000px;"; document.body.appendChild(tester); window.setTimeout(function () { if (tester.offsetHeight === 0) { abd = true; } tester.remove(); }, 100); } catch (e) {} } function executeCommand(commandId, messageId) { if (messageId == pb_message) { switch (commandId) { case 0: //off ************ = originalWindowOpenFn; document.createElement = originalCreateElementFn; document.createEvent = originalCreateEventFn; HTMLElement.prototype.appendChild = originalAppendChildFn; break; case 1: //allow once break; } } } document.addEventListener("fullscreenchange", function () { onFullScreen(document.fullscreen); }, false); document.addEventListener("mozfullscreenchange", function () { onFullScreen(document.mozFullScreen); }, false); document.addEventListener("webkitfullscreenchange", function () { onFullScreen(document.webkitIsFullScreen); }, false); document.addEventListener('DOMContentLoaded', function () { detectAdblock(); }, false); (function () { window.pbExternalCommand = function (commandId, messageId) { executeCommand(commandId, messageId); }; })(); }; inject(); })();*********>
