Excel समय फ़ॉर्मूले: Excel में समय की गणना, फ़ॉर्मेट, और हेरफेर करें

Excel के साथ संख्याओं पर काम करना आसान है, लेकिन समय को संभालना (मेरी राय में) अलग कहानी है। भ्रमित करने वाली बात (जिस पर मैं नीचे चर्चा करूंगा) यह है कि Excel समय मानों को भिन्नात्मक दिनों के रूप में संग्रहीत और प्रोसेस करता है, जो अगर आप परिचित नहीं हैं तो अप्रत्याशित परिणाम दे सकता है।

इस ट्यूटोरियल में, मैं आपको समय मान बनाने और फ़ॉर्मेट करने से लेकर नकारात्मक अवधि और समय ओवरफ़्लो जैसी आम समस्याएँ सुलझाने तक सब कुछ समझाऊंगा। अंत तक, आप प्रोजेक्ट प्लानिंग, पेरोल या जिस भी काम पर आप हैं, उसके लिए अधिक स्मार्ट और भरोसेमंद स्प्रेडशीट बनाना जानेंगे।

Excel के डेट सिस्टम को समझना

हमें Excel के डेट सिस्टम से शुरू करना होगा। Excel तिथियों को क्रमागत सीरियल नंबरों के रूप में संग्रहीत करता है, जो 1 जनवरी, 1900 से शुरू होता है, जिसे मान 1 द्वारा दर्शाया जाता है। प्रत्येक अगला दिन सीरियल नंबर को 1 से बढ़ाता है। उदाहरण के लिए:

• January 1, 1900 = 1

• January 2, 1900 = 2

• December 31, 2023 = 45205

समय मान एक दिन के अंश के रूप में संग्रहीत होते हैं। उदाहरण के लिए:

• 6:00 AM = 0.25 (6 घंटे 24-घंटे के दिन का 25% हैं)

• 12:00 PM = 0.5

• 6:00 PM = 0.75

तिथि और समय को जोड़ना उतना ही सरल है जितना दोनों मानों को जोड़ना। उदाहरण के लिए, नीचे दिया गया फ़ॉर्मूला एक तिथि और समय को जोड़ता है:

=A2 + TIME(10, 30, 0)संचालित द्वारा

यदि A2 में 45205 (December 31, 2023) है, तो परिणाम 45205.4375 होगा, जो 31 दिसंबर, 2023 को सुबह 10:30 का प्रतिनिधित्व करता है।

Excel में समय मान कैसे बनाएं

आइए Excel में वैध समय मान बनाने के दो सबसे आम तरीकों को देखें: TIME() फ़ंक्शन का उपयोग और TIMEVALUE() से टेक्स्ट बदलना।

TIME() फ़ंक्शन का उपयोग करें

मैंने इसका ज़िक्र पहले किया था। TIME() फ़ंक्शन आपको अलग-अलग घटकों: घंटा, मिनट, और सेकंड से समय मान बनाने देता है। इसका सिंटैक्स यह है:

=TIME(hour, minute, second)संचालित द्वारा

उदाहरण के लिए, यदि आप 10:30 AM दर्शाना चाहते हैं, तो आप लिख सकते हैं:

=TIME(10, 30, 0)संचालित द्वारा

यह 10:30 AM का एक सही Excel समय मान बनाता है, जिसे आंतरिक रूप से 0.4375 के रूप में संग्रहीत किया जाता है (क्योंकि 10 घंटे 30 मिनट 24-घंटे के दिन का 43.75% है)।

Excel आपके दर्ज किए गए मानों के साथ लचीला है। यदि आप सामान्य समय सीमाओं से आगे बढ़ते हैं, तो Excel अतिरिक्त समय को रोलओवर कर देता है। उदाहरण के लिए:

=TIME(27, 0, 0)संचालित द्वारा

यह 3:00 AM लौटाता है क्योंकि 27 घंटे एक पूरे दिन (24 घंटे) और 3 घंटे के बराबर होते हैं। इसी तरह:

=TIME(10, 75, 0)संचालित द्वारा

इसे 11:15 AM के रूप में समझा जाता है, क्योंकि 75 मिनट 1 घंटा और 15 मिनट बन जाते हैं।

ये किनारी स्थितियाँ समझना महत्वपूर्ण हैं, खासकर जब आप फ़ॉर्मूलों का उपयोग करके समय मान डायनामिक रूप से जनरेट कर रहे हों।

TIMEVALUE() फ़ंक्शन का उपयोग करें

आप TIMEVALUE() के साथ टेक्स्ट को समय में बदल सकते हैं। तो अगर आपकी स्प्रेडशीट में समय टेक्स्ट के रूप में लिखा है, जैसे “2:30 PM” या “14:45”, तो Excel उसे अपने आप समय मान की तरह नहीं मानेगा। वहीं TIMEVALUE() काम आता है।

=TIMEVALUE("2:30 PM")संचालित द्वारा

यह टेक्स्ट स्ट्रिंग को Excel समय मान में बदल देता है। अब आप इसे किसी भी अन्य समय की तरह गणनाओं में इस्तेमाल कर सकते हैं।

यह ध्यान देने योग्य है कि TIME() और TIMEVALUE() में अंतर क्या है:

• TIME() अलग-अलग संख्याओं (घंटा, मिनट, सेकंड) से समय बनाता है

• TIMEVALUE() टेक्स्ट स्ट्रिंग को समय में बदलता है

दोनों ऐसे मान लौटाते हैं जिन्हें Excel वैध समय के रूप में पहचानता है, जो सटीक गणित और फ़ॉर्मेटिंग के लिए आवश्यक है।

समय का अंतर कैसे निकालें

एक बार जब आपके पास Excel में वैध समय मान होते हैं, तो सबसे आम कामों में से एक दो बिंदुओं के बीच कितना समय बीता है, यह निकालना है। चाहे आप किसी शिफ्ट की लंबाई माप रहे हों या कोई कार्य पूरा करने में लगा समय निकाल रहे हों, Excel आपको उत्तर पाने के कई तरीके देता है।

आइए सबसे उपयोगी तरीकों का पता लगाएँ, सरल घटाव से लेकर उन्नत तरकीबों तक।

दो समय सीधे घटाएँ

समय का अंतर निकालने का सबसे सीधा तरीका है प्रारंभ समय को अंत समय से घटाना। 

उदाहरण के लिए, यदि सेल A1 में 10:00 AM और B1 में 1:30 PM है, तो फ़ॉर्मूला इस तरह होगा:

=B1 - A1संचालित द्वारा

परिणाम 0.145833 होगा। यह पहले अजीब लग सकता है, लेकिन याद रखें कि Excel समय को दिन के अंश के रूप में दर्शाता है। इस मामले में, 0.145833 3 घंटे 30 मिनट के बराबर है।

परिणाम को अधिक उपयोगी बनाने के लिए, आप आमतौर पर इसे मानव-पठनीय फ़ॉर्मेट में बदलना चाहेंगे।

समय के अंतर को घंटे, मिनट, या सेकंड में बदलें

आप घटाव के परिणाम को गुणा करके अंतर को घंटे, मिनट या सेकंड में व्यक्त कर सकते हैं। यह रहा समय अंतर को घंटों में बदलने का तरीका:

=(B2 - A2) * 24संचालित द्वारा

यह रहा समय अंतर को मिनटों में बदलने का तरीका:

=(B2 - A2) * 1440संचालित द्वारा

यह रहा समय अंतर को सेकंड में बदलने का तरीका:

=(B2 - A2) * 86400संचालित द्वारा

ये फ़ॉर्मूले इसलिए काम करते हैं क्योंकि एक दिन में 24 घंटे, 1,440 मिनट, और 86,400 सेकंड होते हैं। यदि समय का अंतर साढ़े तीन घंटे है, तो आपको घंटों वाले फ़ॉर्मूले से 3.5, मिनटों वाले से 210, और सेकंड वाले से 12,600 मिलेगा।

विशिष्ट समय इकाइयाँ निकालें

अगर आप केवल समय का एक भाग निकालना चाहते हैं, जैसे सिर्फ मिनट या घंटे, तो आप HOUR() का उपयोग कर सकते हैं। HOUR() किसी समय का घंटे वाला भाग लौटाता है।

=HOUR(B2 - A2)संचालित द्वारा

MINUTE() मिनट वाला भाग लौटाता है।

=MINUTE(B2 - A2)संचालित द्वारा

SECOND() सेकंड लौटाता है।

=SECOND(B2 - A2)संचालित द्वारा

ये फ़ंक्शन उपयोगी हैं, लेकिन 23 घंटे, 59 मिनट या 59 सेकंड के बाद रीसेट हो जाते हैं। इसलिए यदि अवधि एक पूरे दिन से लंबी है, तो वे कुल बीता समय नहीं दिखाएंगे। ऐसे मामलों में, अंतर को गुणा करना या कस्टम फ़ॉर्मेट का उपयोग करना बेहतर है, जिसे हम आगे कवर करेंगे।

24 घंटे से अधिक समय जोड़ें

यदि आपको 24 घंटे से अधिक घंटे जोड़ने हैं, तो सीधे TIME() का उपयोग अपेक्षित रूप से काम नहीं करेगा। इसके बजाय, आप दिन के अंशों का उपयोग करके अंकगणित कर सकते हैं।

यहाँ 24 से अधिक घंटे जोड़ने का तरीका है।

=A2 + (28/24)  ' Adds 28 hoursसंचालित द्वारा

यहाँ 60 से अधिक मिनट जोड़ने का तरीका है।

=A2 + (90/1440)  ' Adds 90 minutesसंचालित द्वारा

यहाँ 60 से अधिक सेकंड जोड़ने का तरीका है।

=A2 + (3600/86400)  ' Adds 3600 secondsसंचालित द्वारा

Excel समय को दशांश के रूप में संग्रहीत करता है, जहाँ 1 दिन = 1 होता है, इसलिए 24, 1440, या 86400 से भाग देने पर मान दिन के हिस्से के रूप में समायोजित हो जाता है।

नकारात्मक समय मानों को संभालें

डिफ़ॉल्ट रूप से, यदि आप छोटे समय से बड़े समय को घटाते हैं (जैसे, 10:00 AM - 2:00 PM), तो Excel त्रुटि लौटाएगा या ### की स्ट्रिंग दिखाएगा।

नकारात्मक समय मानों को सुरक्षित रूप से संभालने के लिए, MOD() फ़ंक्शन का उपयोग करें। यह फ़ंक्शन नकारात्मक परिणामों को प्रभावी रूप से रोकता है, उन्हें वैध समय पर रैप करके।

उदाहरण के लिए, 3 घंटे सुरक्षित रूप से घटाने के लिए:

=MOD(A2 - TIME(3, 0, 0), 1)संचालित द्वारा

यह फ़ॉर्मूला सुनिश्चित करता है कि परिणाम हमेशा एक सकारात्मक समय मान होगा, जिससे अवांछित त्रुटियाँ या डिस्प्ले समस्याएँ रुकेंगी।

समय के परिणामों को सही तरीके से फ़ॉर्मेट कैसे करें

Excel में समय गणनाएँ अक्सर भ्रमित करने वाले दशमलव मान या अप्रत्याशित परिणाम देती हैं, खासकर जब 24 घंटे से अधिक की अवधि या नकारात्मक समय मानों से निपटते हैं। उचित फ़ॉर्मेटिंग कच्चे आउटपुट को पठनीय, उपयोगकर्ता-मित्रवत फ़ॉर्मेट में बदल सकती है, जिन्हें समझना और प्रस्तुत करना आसान हो।

बेसिक समय फ़ॉर्मेटिंग का उपयोग

यदि आप दशमलव के बजाय “3:30” जैसा समय अंतर दिखाना चाहते हैं, तो TEXT() फ़ंक्शन का उपयोग करें।

=TEXT(B2 - A2, "h:mm")संचालित द्वारा

यह परिणाम को घंटे और मिनट के रूप में फ़ॉर्मेट करता है। ध्यान रखें कि आउटपुट टेक्स्ट होता है, जो दिखने में अच्छा लगता है, लेकिन जब तक आप उसे वापस कनवर्ट नहीं करते, आप उसे आगे की गणनाओं में उपयोग नहीं कर पाएंगे।

कस्टम समय फ़ॉर्मेट लागू करना

Excel के बिल्ट-इन समय फ़ॉर्मेट मानक समय मानों को प्रभावी ढंग से संभालते हैं, लेकिन कस्टम फ़ॉर्मेट अवधि, विस्तारित घंटे, या विशिष्ट समय घटकों को दिखाने के लिए अधिक लचीलापन देते हैं।

कस्टम समय फ़ॉर्मेट लागू करने के लिए, ये कदम अपनाएँ:

1. समय मान वाले सेल(सेलों) का चयन करें।
2. राइट-क्लिक करें और Format Cells चुनें।
3. Number टैब में Custom चुनें।
4. वांछित फ़ॉर्मेट कोड दर्ज करें।

यहाँ कुछ सामान्य कस्टम समय फ़ॉर्मेट हैं:

• [h]:mm: कुल घंटे और मिनट दिखाता है, 24 घंटे से आगे भी। उदाहरण: 36:45 (36 घंटे 45 मिनट)

• d "days" h:mm:ss: दिन, घंटे, मिनट, और सेकंड दिखाता है। उदाहरण: 1 days 12:30:00

• h:mm AM/PM: AM/PM नोटेशन के साथ समय दिखाता है। उदाहरण: 10:30 PM

• mm:ss: केवल मिनट और सेकंड दिखाता है। उदाहरण: 45:15

कस्टम फ़ॉर्मेट आपको समय डेटा को ऐसे तरीकों से प्रस्तुत करने देते हैं जो टाइमशीट या वर्क लॉग जैसे विशिष्ट उपयोग मामलों के लिए अधिक सहज हों।

दोस्ताना समय अंतर दिखाना

यदि आप समय का अंतर “2 days, 4 hours, and 15 minutes” जैसी मानव-पठनीय स्ट्रिंग के रूप में दिखाना चाहते हैं, तो आप एक ही फ़ॉर्मूले में कई फ़ंक्शनों को जोड़ सकते हैं।

यहाँ एक व्यावहारिक उदाहरण है:

=INT(B2 - A2) & " days, " & HOUR(B2 - A2) & " hours, " & MINUTE(B2 - A2) & " minutes"संचालित द्वारा

इस फ़ॉर्मूले में, INT() पूरे दिनों की संख्या निकालता है, जबकि HOUR() और MINUTE() शेष घंटे और मिनट निकालते हैं।

आउटपुट में शून्य मान आने से रोकने के लिए, आप इन गणनाओं को IF() स्टेटमेंट्स में नेस्ट कर सकते हैं। उदाहरण के लिए:

=IF(INT(B2 - A2) > 0, INT(B2 - A2) & " days, ", "") &
 IF(HOUR(B2 - A2) > 0, HOUR(B2 - A2) & " hours, ", "") &
 MINUTE(B2 - A2) & " minutes"संचालित द्वारा

यह फ़ॉर्मूला केवल प्रासंगिक इकाइयाँ दिखाता है, जिससे आउटपुट साफ़ और अधिक पठनीय हो जाता है।

जानने योग्य प्रमुख Excel समय फ़ंक्शन

जैसा कि आपने अब तक देखा, Excel में समय फ़ंक्शनों का एक शक्तिशाली सेट है। अधिक पूर्ण विवरण के हित में, यहाँ सबसे महत्वपूर्ण Excel समय फ़ंक्शनों का त्वरित सारांश है।

समय मान बनाने के लिए TIME() का उपयोग

TIME() तीन घटकों: घंटे, मिनट, और सेकंड से वैध समय मान बनाता है। जब आपको प्रोग्रामेटिक रूप से समय मान बनाना हो, यह फ़ंक्शन आदर्श है।

यहाँ एक अच्छा उदाहरण है:

=TIME(14, 30, 0)  संचालित द्वारा

ऊपर वाला 2:30 PM लौटाएगा, जो इस सिंटैक्स का परिणाम है।

=TIME(hour, minute, second)संचालित द्वारा

TIMEVALUE() से टेक्स्ट को समय में बदलना

यदि आपके पास समय टेक्स्ट स्ट्रिंग (जैसे, "2:45 PM") के रूप में लिखा है, तो TIMEVALUE() का उपयोग करके उसे ऐसा Excel समय मान बनाएं जिसे गणनाओं में उपयोग किया जा सके।

=TIMEVALUE("2:45 PM")संचालित द्वारा

उदाहरण के लिए, यह 0.614583 लौटाएगा जो एक दिन का अंश है:

=TIMEVALUE("14:45")संचालित द्वारा

NOW() से वर्तमान तिथि और समय प्राप्त करना

NOW() वर्तमान तिथि और समय एक ही मान के रूप में लौटाता है। यह हर बार वर्कशीट के पुनर्गणना होने पर स्वचालित रूप से अपडेट होता है।

=NOW()संचालित द्वारा

यहाँ एक अच्छा उदाहरण है। यदि वर्तमान समय 3:15 PM है, तो NOW() 44710.63542 जैसा कुछ लौटा सकता है, जहाँ 44710 तिथि है और .63542 समय दर्शाता है।

TODAY() के साथ वर्तमान तिथि प्राप्त करना

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

=TODAY()संचालित द्वारा

HOUR(), MINUTE(), और SECOND() से समय के भाग निकालना

ये फ़ंक्शन आपको समय मान के विशिष्ट भागों को अलग करने देते हैं, जिससे वे रिपोर्टिंग और कंडीशनल लॉजिक के लिए उपयोगी बनते हैं।

HOUR() समय मान का घंटे वाला भाग (0-23) लौटाता है।

=HOUR(A2)संचालित द्वारा

MINUTE() मिनट वाला भाग (0-59) लौटाता है।

=MINUTE(A2)संचालित द्वारा

SECOND() सेकंड वाला भाग (0-59) लौटाता है।

=SECOND(A2)संचालित द्वारा

TEXT() के साथ समय मानों को फ़ॉर्मेट करना

TEXT() किसी समय मान को एक विशेष फ़ॉर्मेट में बदल देता है, जो अवधि दिखाने या कस्टम समय स्ट्रिंग बनाने के लिए आदर्श है। सिंटैक्स:

=TEXT(A2, "h:mm:ss AM/PM")संचालित द्वारा

TEXT() के साथ नकारात्मक समय मान संभालना

समय मानों को घटाते समय, MOD() सुनिश्चित करता है कि परिणाम हमेशा सकारात्मक रहे, जिससे नकारात्मक समय मानों के कारण होने वाली त्रुटियाँ रोकी जा सकें।

=MOD(A2 - B2, 1)संचालित द्वारा

DATE() के साथ तिथि मान बनाना

DATE() आपको अलग-अलग घटकों: वर्ष, महीना, और दिन से तिथि बनाने देता है। यह तिथि और समय को एक ही मान में जोड़ने के लिए उपयोगी है।

=DATE(year, month, day)संचालित द्वारा

यहाँ एक अच्छा उदाहरण है:

=DATE(2023, 12, 31) + TIME(14, 30, 0)संचालित द्वारा

जो 12/31/2023 2:30 PM लौटाता है।

NETWORKDAYS() के साथ कार्यदिवस बनाना

NETWORKDAYS() दो तिथियों के बीच कार्यदिवसों की संख्या निकालता है, सप्ताहांत और वैकल्पिक रूप से निर्दिष्ट छुट्टियों को छोड़कर। यह फ़ंक्शन प्रोजेक्ट की अवधि या पेरोल पीरियड की गणना के लिए आदर्श है।

=NETWORKDAYS(start_date, end_date, [holidays])संचालित द्वारा

उदाहरण के लिए:

=NETWORKDAYS(A2, B2, C2:C5)संचालित द्वारा

यदि A2 में 12/1/2023 और B2 में 12/31/2023 है, तो परिणाम दिसंबर में कार्यदिवसों की संख्या होगी, C2:C5 श्रेणी में सूचीबद्ध छुट्टियों को छोड़कर।

WORKDAY.INTL() के साथ कार्यदिवस और सप्ताहांत बनाना

WORKDAY.INTL() WORKDAY() का उन्नत संस्करण है, जो आपको यह कस्टमाइज़ करने देता है कि कौन से दिन सप्ताहांत माने जाएँ।

=WORKDAY.INTL(start_date, days, [weekend], [holidays])संचालित द्वारा

उदाहरण के लिए:

=WORKDAY.INTL(A2, 10, "0110000")संचालित द्वारा

यहाँ, 0110000 निर्दिष्ट करता है कि मंगलवार (0) और बुधवार (1) सप्ताहांत हैं। फ़ॉर्मूला A2 के 10 कार्यदिवस बाद की तिथि की गणना करता है, मंगलवार और बुधवार को छोड़कर।

निष्कर्ष

Excel में समय को संभालना जटिल लग सकता है, लेकिन सही फ़ंक्शनों के साथ यह कहीं अधिक प्रबंधनीय हो जाता है।

यदि आप अपनी Excel कौशल को और मजबूत करना चाहते हैं, तो हमारे Introduction to Excel को बुनियादी कौशलों के लिए देखें या वास्तविक-विश्व डेटा के साथ अभ्यास करने हेतु Data Analysis in Excel कोर्स देखें।

और अधिक उन्नत अनुप्रयोगों के लिए, Excel Fundamentals स्किल ट्रैक गाइडेड प्रोजेक्ट्स और अभ्यास देता है ताकि आप समय गणनाओं से आगे भी महारत हासिल कर सकें।