एक्सेल सूत्र: तारीखों के बीच काम के घंटे प्राप्त करें कस्टम शेड्यूल -

सामान्य सूत्र

=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))

सारांश

कस्टम शेड्यूल के साथ दो तिथियों के बीच काम के घंटे की गणना करने के लिए, आप ROW, INDIRECT और MID की मदद से WEEKDAY और SUMPRODUCT फ़ंक्शंस के आधार पर एक सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, F8 में सूत्र है:

=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))

जो एक कस्टम शेड्यूल के आधार पर 36 घंटे देता है, जहां 8 घंटे काम किया जाता है सोम-शुक्र, 4 घंटे शनिवार को काम किया जाता है, और सोमवार 3 सितंबर को एक छुट्टी है। छुट्टियों को नामित सीमा G6: G8 के रूप में आपूर्ति की जाती है। कार्य अनुसूची को कॉलम डी में एक पाठ स्ट्रिंग के रूप में दर्ज किया गया है और इसे वांछित के रूप में बदला जा सकता है।

नोट: यह एक सरणी सूत्र है जिसे नियंत्रण + Shift + Enter के साथ दर्ज किया जाना चाहिए। यदि आपके पास मानक 8 घंटे का कार्यदिवस है, तो यह सूत्र सरल है।

स्पष्टीकरण

मूल रूप से, यह सूत्र दो दिए गए तिथियों के बीच हर दिन के लिए सप्ताह के दिन (यानी सोमवार, मंगलवार, आदि) का पता लगाने के लिए WEEKDAY फ़ंक्शन का उपयोग करता है। WEEKDAY, डिफ़ॉल्ट सेटिंग्स के साथ, रविवार = 1 और शनिवार = 7 के बीच 1 और 7 के बीच एक नंबर देता है।

इस फॉर्मूले के लिए ट्रिक डेट्स का एक एसेम्बल बना रही है जिसे आप WEEKDAY फ़ंक्शन में फीड कर सकते हैं। यह ROW के साथ संकेत के साथ किया जाता है:

ROW(INDIRECT(B6&":"&C6))

ROW संक्षिप्त संख्या को पंक्ति संख्या के रूप में व्याख्या करता है और इस तरह एक सरणी देता है:

(43346;43347;43348;43349;43350;43351;43352)

सरणी में प्रत्येक संख्या एक तारीख का प्रतिनिधित्व करती है। WEEKDAY फ़ंक्शन तब सरणी का मूल्यांकन करता है और कार्यदिवस मानों की एक सरणी लौटाता है:

(2;3;4;5;6;7;1)

ये संख्या प्रत्येक तिथि के सप्ताह के दिन के अनुरूप है। उन्हें प्रारंभ संख्या तर्क के रूप में MID फ़ंक्शन के लिए प्रदान किया जाता है, साथ ही D6 में मान, पाठ के लिए "0888884":

MID("0888884",(2;3;4;5;6;7;1),1)

क्योंकि हम MID को प्रारंभ संख्याओं का एक सरणी दे रहे हैं, यह इस तरह के परिणामों की एक सरणी देता है:

("8";"8";"8";"8";"8";"4";"0")

ये मूल्य आरंभ तिथि से लेकर अंतिम तिथि तक प्रत्येक दिन काम किए गए घंटों के अनुरूप हैं। ध्यान दें कि इस सरणी में मान पाठ हैं, संख्या नहीं। वास्तविक संख्याओं में बदलने के लिए, हम छुट्टियों को प्रबंधित करने के लिए बनाए गए दूसरे सरणी से गुणा करते हैं, जैसा कि नीचे बताया गया है। गणित ऑपरेशन पाठ को संख्यात्मक मानों के साथ जोड़ता है।

छुट्टियाँ

छुट्टियों को संभालने के लिए, हम इस तरह ISNA, MATCH और नामित श्रेणी "छुट्टियों" का उपयोग करते हैं:

ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))

यह अभिव्यक्ति उन तिथियों का पता लगाने के लिए MATCH का उपयोग करती है, जो नामित रेंज छुट्टियों में INDIRECT और ROW के साथ उत्पन्न तारीखों की समान सरणी का उपयोग करते हैं। जब छुट्टियां मिलें और # N / A त्रुटि न हो तो MATCH एक नंबर देता है। ISNA फ़ंक्शन परिणामों को "फ़्लिप" करता है ताकि TRUE छुट्टियों का प्रतिनिधित्व करता है और FALSE गैर-छुट्टियों का प्रतिनिधित्व करता है। ISNA एक सरणी देता है या इस तरह से परिणाम देता है:

(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)

अंत में, दोनों सरणियों को SUMPRODUCT के अंदर एक दूसरे से गुणा किया जाता है। गणित ऑपरेशन TRUE और FALSE को 1 और शून्य, और पाठ मानों को पहले मानों में संख्यात्मक मानों (जैसा कि ऊपर बताया गया है) के साथ जोड़ता है, इसलिए अंत में हमारे पास है:

=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))

गुणा के बाद, हमारे पास SUMPRODUCT के अंदर एक एकल सरणी है, जिसमें तिथि सीमा के सभी कार्य समय हैं:

=SUMPRODUCT((0;8;8;8;8;4;0))

SUMPRODUCT तब सरणी में सभी आइटमों को भेजता है और 36 का परिणाम देता है।