एक्सेल ट्यूटोरियल: एक्सपायरी डेट की गणना और हाइलाइट कैसे करें

विषय - सूची

संबंधित शॉर्टकट

इस वीडियो में, हम समाप्ति तिथियों की गणना और हाइलाइट करने का तरीका देखेंगे।

मान लीजिए कि आपकी कंपनी ने किसी तरह का सदस्यता कार्यक्रम शुरू किया है और आपके बॉस ने आपको डेटा का एक सेट भेजा है। उसने आपको 1000 लोगों की एक सूची दी है, जिन्होंने पिछले वर्ष या उसके बाद सदस्यता की नवीनीकरण की है, और वह कई चीजों की तलाश में है।

सबसे पहले, वह चाहती है कि आप भविष्य में एक वर्ष की समाप्ति तिथि की गणना करें, उसी महीने के अंतिम दिन सदस्यता को नवीनीकृत किया गया था।

दूसरा, वह यह देखना चाहती है कि समाप्ति से पहले कितने दिन रहेंगे।

तीसरा, वह पहले से ही समाप्त हो चुकी किसी भी सदस्यता के लिए "समाप्त" की स्थिति देखना चाहती है, और अगले 30 दिनों में समाप्त होने वाली किसी भी सदस्यता के लिए "जल्द ही समाप्त हो रही है"।

अंत में, उसने कहा कि यह अच्छा होगा कि समय सीमा समाप्त हो चुके सदस्यों को गुलाबी रंग में उजागर किया जाए, और जल्द ही समाप्त होने वाले लोगों को पीले रंग में उजागर किया जाए।

इसके अलावा - क्या वह दोपहर को अपनी दोपहर की बैठक से पहले कर सकती है?

उम्म, ज़रूर।

पहले, आइए इस डेटा को एक उचित एक्सेल टेबल में बदलें। यह फ़ॉर्मूला दर्ज करने में बहुत आसान बना देगा, क्योंकि एक्सेल स्वचालित रूप से हमें कॉपी कर देगा, जैसा कि हम जाते हैं।

अब हम समाप्ति तिथियों की गणना करते हैं। ये एक साल बाद उसी महीने के अंत में होने चाहिए, लेकिन चलो पहले एक साधारण हैक का उपयोग करते हैं, बॉलपार्क में प्राप्त करने के लिए। जैसा कि आप पहले के वीडियो में देख चुके हैं कि तारीखें सिर्फ सीरियल नंबर हैं, इसलिए हम सिर्फ एक फॉर्मूला दर्ज कर सकते हैं जो 365 दिनों को नवीनीकृत तिथि में जोड़ता है।

यह एक अच्छी शुरुआत है। हम समाधान को समाप्त कर सकते हैं और बाद में इसे ठीक करने के लिए वापस आ सकते हैं।

जब आप एक्सेल में अधिक जटिल समस्या को हल कर रहे हैं तो समग्र दृष्टिकोण को मान्य करने के लिए यह एक अच्छा विचार है, फिर अंत में विवरण पर वापस आएं। आप शुरुआत में एक छोटी सी बात पर अटकना नहीं चाहते, खासकर अगर दृष्टिकोण बदल सकता है।

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

सूत्र बस E5 माइनस टुडे () है। जब मैं रिटर्न मारता हूं, तो हमें समाप्ति से पहले दिन शेष मिलते हैं। नकारात्मक संख्या एक सदस्यता को पहले से ही समाप्त हो चुकी है।

स्थिति के लिए, हम एक सरल नेस्टेड IF सूत्र का उपयोग करेंगे। यदि शेष दिन शून्य से कम है, तो सदस्यता समाप्त हो गई है। अन्यथा, यदि दिन बचे हैं 30 से कम है, तो स्थिति "जल्द ही समाप्त हो" होनी चाहिए। अन्यथा, स्थिति कुछ भी नहीं है।

=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))

अगला हमें सशर्त स्वरूपण नियम बनाने की आवश्यकता है जो इन मूल्यों को उजागर करते हैं।

सबसे पहले, डेटा का चयन करें, और सक्रिय सेल को ऊपरी दाएं कोने पर सेट करें। फिर, एक सूत्र नियम बनाएँ जो "एक्सपायर्ड" मूल्य के लिए सक्रिय सेल का परीक्षण करता है। कॉलम को लॉक करना होगा।

अब सदस्यता के लिए वही प्रक्रिया दोहराएं जो "जल्द ही समाप्त हो रही हैं"।

यह अच्छा लग रहा है। हमें बस महीने के आखिरी दिन उतरने के लिए समाप्ति की तारीख तय करनी होगी।

ठीक है, यह पता चलता है कि ईओएमएनटीएच (महीने के अंत के लिए) नामक एक शांत फ़ंक्शन है जो पिछले या भविष्य में एक महीने का आखिरी दिन होता है।

प्रारंभ दिनांक, तिथि नवीनीकृत है और महीने 12 है।

और वहां आपके पास वह सब कुछ है जो आपके बॉस चाहते थे, और आप अभी भी दोपहर के भोजन से पहले एक कप कॉफी के लिए समय देते हैं।