एक्सेल सूत्र: पाठ को दिनांक में बदलें -

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

=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))

सारांश

किसी अपरिचित तिथि प्रारूप में पाठ को उचित Excel दिनांक में परिवर्तित करने के लिए, आप पाठ को पार्स कर सकते हैं और कई कार्यों के आधार पर एक सूत्र के साथ एक उचित तिथि को इकट्ठा कर सकते हैं: DATE, LEFT, MID, और RIGHT। दिखाए गए उदाहरण में, C6 में सूत्र है:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

यह सूत्र वर्ष, महीने और दिन के मानों को अलग-अलग निकालता है, और दिनांक 24 अक्टूबर, 2000 को इकट्ठा करने के लिए DATE फ़ंक्शन का उपयोग करता है।

पृष्ठभूमि

जब आप किसी अन्य सिस्टम के डेटा के साथ काम कर रहे होते हैं, तो आप ऐसी स्थिति में भाग सकते हैं, जहाँ तारीखों को एक्सेल द्वारा ठीक से पहचाना नहीं जाता है, जो पाठ की तरह तारीखों को मानता है। उदाहरण के लिए, आपके पास इस तरह के पाठ मूल्य हो सकते हैं:

पाठ	तारीख का प्रतिनिधित्व किया
20001024	24 अक्टूबर, 2000
20050701	1 जुलाई, 20115
19980424 है	24 अप्रैल, 1998
28.02.2014	28 फरवरी, 2014

जब Excel ने पाठ के रूप में दिनांक मान का मूल्यांकन किया है, तो एक विकल्प यह है कि पाठ को अपने घटकों (वर्ष, माह, दिन) में पार्स करने के लिए एक सूत्र का उपयोग करें और DATE फ़ंक्शन के साथ एक तिथि बनाने के लिए इनका उपयोग करें। जैसा कि ऊपर उल्लेख किया गया है, मैं आपको सलाह देता हूं कि पहले आप किसी सूत्र का उपयोग करने से पहले नीचे दिए गए समाधानों को आज़माएं (शून्य और कॉलम में पाठ का उपयोग करके)। दोनों वर्कअराउंड तेज हैं और कम प्रयास की आवश्यकता है।

स्पष्टीकरण

DATE फ़ंक्शन तीन तर्कों का उपयोग करके एक मान्य दिनांक बनाता है: वर्ष, महीना और दिन:

=DATE(year,month,day)

सेल C6 में, हम इनमें से प्रत्येक घटक को टेक्स्ट स्ट्रिंग से निकालने के लिए LEFT, MID और RIGHT फ़ंक्शन का उपयोग करते हैं और परिणाम DATE फ़ंक्शन में फ़ीड करते हैं:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

LEFT फ़ंक्शन वर्ष के लिए सबसे बाईं ओर के 4 वर्णों को निकालता है, MID फ़ंक्शन महीने के लिए 5-6 स्थितियों में वर्णों को निकालता है, और RIGHT फ़ंक्शन दिन के रूप में सबसे सही 2 वर्णों को निकालता है। प्रत्येक परिणाम सीधे DATE फ़ंक्शन पर वापस आ जाता है। अंतिम परिणाम एक उचित एक्सेल तिथि है जिसे किसी भी तरह से स्वरूपित किया जा सकता है।

इस दृष्टिकोण को आवश्यकतानुसार अनुकूलित किया जा सकता है। उदाहरण के लिए, पंक्ति 8 में अपरिचित दिनांक प्रारूप dd.mm.yyyy और C8 में सूत्र है:

=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))

लंबा रूप पाठ

कभी-कभी आपके पास "अप्रैल 11 2020 08:43:13" जैसे लंबे रूप में तारीखें हो सकती हैं, जो एक्सेल ठीक से नहीं पहचानता है। इस स्थिति में, आप स्ट्रिंग को इस तरह से समायोजित करने में सक्षम हो सकते हैं, जिससे Excel SUBSTITUTE फ़ंक्शन के साथ दिनांक को सही ढंग से पहचान सके। नीचे दिया गया सूत्र, अल्पविराम और स्थान (",") के साथ अंतरिक्ष के दूसरे उदाहरण ("") को प्रतिस्थापित करता है:

=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month

एक बार जब हम महीने के नाम के बाद अल्पविराम जोड़ते हैं, तो एक्सेल तारीख को समझ जाएगा, लेकिन अभी भी इसे "किक" की आवश्यकता है। इसलिए हम अंत में शून्य जोड़ते हैं। गणित ऑपरेशन से स्ट्रिंग को संख्या में बदलने और बदलने की कोशिश होती है। सफल होने पर, यह एक मान्य एक्सेल तारीख में परिणाम देगा। नोट आपको तिथि को सही ढंग से प्रदर्शित करने के लिए दिनांक संख्या स्वरूपण लागू करने की आवश्यकता हो सकती है।

बिना सूत्रों के

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

तारीखें तय करने के लिए शून्य जोड़ें

कभी-कभी, आप एक पाठ प्रारूप में तारीखों का सामना करेंगे जिसे एक्सेल को पहचानना चाहिए। इस स्थिति में, आप एक्सेल को मूल्य में शून्य जोड़कर पाठ मानों को तिथियों में बदलने के लिए मजबूर कर सकते हैं। जब आप शून्य जोड़ते हैं, तो Excel पाठ मानों को संख्याओं के साथ जोड़ने का प्रयास करेगा। चूँकि तारीखें सिर्फ संख्याएँ हैं, यह चाल पाठ प्रारूप में तारीखों को परिवर्तित करने का एक शानदार तरीका है जिसे एक्सेल को वास्तव में समझना चाहिए।

शून्य जोड़कर तिथियों को परिवर्तित करने के लिए, विशेष चिपकाएँ:

1. अप्रयुक्त सेल में शून्य (0) दर्ज करें और क्लिपबोर्ड पर कॉपी करें
2. समस्याग्रस्त तिथियों का चयन करें
3. विशेष पेस्ट> मान> जोड़ें
4. दिनांक प्रारूप लागू करें (यदि आवश्यक हो)

आप इस तरह एक सूत्र में शून्य भी जोड़ सकते हैं:

=A1+0

जहाँ A1 में एक अपरिचित तिथि होती है।

तारीख तय करने के लिए कॉलम का पाठ

एक्सेल को तारीखों को पहचानने का एक और तरीका है, टेक्स्ट टू कॉलम फॉर्म का उपयोग करना:

तिथियों के कॉलम का चयन करें, फिर डेटा> टेक्स्ट टू कॉलम> फिक्स्ड> फिनिश आज़माएं

यदि एक्सेल तारीखों को पहचानता है, तो यह उन सभी को एक चरण में ठीक कर देगा।