جداسازی روز و ماه و سال از تاریخ شمسی در اکسل + افزونه

سلام دوستان عزیز وقتتون بخیر؛ آرزوی اوقات خوشی رو براتون داریم 🙂 در این جلسه از سری آموزش های پویش پایدار در خدمتتون هستیم برای آموزش نحوه جداسازی روز، ماه و سال از تاریخ شمسی در اکسل.

نحوه استخراج روز و ماه و سال از تاریخ شمسی در اکسل

همانطورکه در پست های قبلی گفتیم، زمانیکه یک تاریخ شمسی را در سلول اکسل وارد می کنید، فرمت آن سلول General است اما اگر یک تاریخ میلادی (بالاتر از سال 1900) را وارد کنید، فرمت آن سلول به صورت خودکار Date می شود. این بدان معناست که اکسل درک درستی از تاریخ های شمسی ندارد و آن ها را به عنوان متن تشخیص می دهد. لذا اگر قصد جداسازی روز و ماه و سال از یک تاریخ شمسی در اکسل را داشته باشیم، تابعی برای این منظور وجود ندارد اما برای تاریخ های میلادی سه تابع به نام های MONTH ،DAY و YEAR موجود است که برای تاریخ های شمسی کار نمی کند.

 

 

 

راهکارهای تفکیک اجزاء تاریخ در اکسل

برای استخراج روز، ماه و سال از یک تاریخ شمسی در اکسل، دو راهکار وجود دارد که در ادامه به شرح آن ها پرداخته ایم:

 

راهکار اول) استخراج روز و ماه و سال از تاریخ شمسی با استفاده از فرمول نویسی در اکسل

مطابق تصویر زیر، در سلول ها B3 تا B16 تاریخ های شمسی درج شده است. برای جدا کردن سال و ماه و روز از این تاریخ های شمسی ابتدا فرمول را برای سلول B3  می نویسیم و برای سایر سلول ها Autofill می کنیم.

 

جدا کردن سال از تاریخ شمسی در اکسل

در این مثال، قسمت سال تمامی تاریخ های وارد شده دارای 4 کاراکتر عددی می باشد. بنابراین با استفاده از تابع Left، 4 کاراکتر اول را از تاریخ های شمسی جدا می کنیم و بدین ترتیب عملیات جداسازی سال از تاریخ شمسی در اکسل انجام می شود.

 

جداسازی ماه از تاریخ شمسی در اکسل

همانطورکه در تصویر زیر مشخص است، تعداد کاراکترهای قسمت ماه تاریخ های شمسی یکسان نیستند و بعضی تاریخ ها دارای یک کاراکتر و بعضی دیگر دارای دو کاراکتر هستند. بنابراین اگر بخواهیم با استفاده از تابع MID از وسط این تاریخ ها ماه را جدا کنیم، نمی توانیم به صورت زیر فرمول نویسی کنیم:

MID(B3,6,1)

آرگومان های اول و دوم فرمول فوق درست است اما آرگومان سوم درست نیست. در آرگومان اول سلول B3 به عنوان متن درج شده است. در آرگومان دوم نیز محل جداسازی از آرگومان ششم تعیین شده است (زیرا قسمت ماه تمامی تاریخ ها از آرگومان ششم شروع می شود) اما آرگومان سوم تابع MID که تعداد کاراکترهای مورد نظر برای جداسازی ماه از تاریخ شمسی است، عدد 1 وارد شده و در صورتیکه قسمت ماه یک تاریخ شمسی دو رقمی باشد، فرمول فوق فقط رقم اول ماه را جدا می کند. بنابراین آرگومان سوم باید برابر با تعداد کاراکترهای ماه تاریخ باشد.

برای تشخیص تعداد کاراکترهای ماه یک تاریخ شمسی باید در نظر داشته باشیم که قسمت ماه تاریخ مابین دو کاراکتر “/” قرار گرفته است. پس اگر جایگاه اسلش اول و اسلش دوم در تاریخ شمسی را پیدا کنیم و این دو را از هم کم کرده و منهای 1 کنیم، تعداد کاراکترهای قسمت ماه تاریخ شمسی به دست می آید. برای مثال تاریخ 1402/07/14 را در نظر داشته باشید. جایگاه اسلش اول 5 و جایگاه اسلش دوم 8 است و با تفریق این دو جایگاه از هم تفریق حاصل از عدد 1، تعداد کاراکترهای قسمت ماه تاریخ یعنی عدد 2 به دست می آید.

حالا باید موارد ذکر شده را به صورت فرمول اکسل در بیاوریم. برای این منظرو باید از تابع FIND استفاده کنیم.

 

تابع FIND در اکسل

تابع FIND برای جستجوی جایگاه یک کاراکتر در یک متن استفاده می شود. این تابع دو آرگومان اجباری و یک آرگومان اختیاری دارد.

آرگومان اول: کاراکتر مورد جستجو         آرگومان دوم: متن مورد نظر           آرگومان سوم: محل شروع جستجو

بنابراین ابتدا با استفاده از فرمول FIND(“/”B3) جایگاه اسلش اول و باید استفاده از فرمول FIND(“/”,B3,6) جایگاه اسلش دوم را به دست می آوریم و تعداد کاراکترهای قسمت ماه تاریخ شمسی را به صورت تصویر زیر فرمول نویسی می کنیم:

 

 

حال فرمول فوق را به عنوان آرگومان سوم تابع MID قرار می دهیم و با استفاده از فرمول زیر جداسازی ماه از تاریخ شمسی انجام می شود:

 

 

با تعمیم فرمول فوق برای سلول های پایینتر، استخراج ماه از تاریخ شمسی در اکسل انجام می شود:

 

 

جداسازی روز از تاریخ شمسی در اکسل

برای جداسازی روز از تاریخ شمسی در اکسل نیز ابتدا باید تعداد کاراکترهای بخش روز تاریخ شمسی را به دست آوریم. برای این منظور باید تعداد کاراکترهای تاریخ را از جایگاه اسلش دوم کم کنیم. در تصویر زیر این فرمول را مشاهده می کنید:

 

 

حال فرمول فوق را به عنوان کاراکتر دوم تابع Right قرار می دهیم تا جداسازی روز از تاریخ شمسی در اکسل انجام شود:

 

 

در تصویر زیر، نتیجه استخراج روز و ماه و سال از تاریخ شمسی در اکسل را مشاهده می کنید:

 

 

راهکار دوم) استخراج اجزا تاریخ شمسی در اکسل با استفاده از افزونه

با تهیه و نصب افزونه تقویم و تاریخ شمسی در اکسل پویش پایدار توابع ShamsiYear، ShamsiMonth، ShamsiDay به لیست توابع اکسل شما افزوده خواهد شد. این سه تابع تک آرگومانه هستند و تاریخ شمسی را به عنوان آرگومان می گیرند و به ترتیب روز، ماه و سال را از آن تاریخ جدا می کنند.

 

 

 

 

تفاوت فرمول نویسی و افزونه برای جدا کردن بخش های تاریخ شمسی از هم

1) فرض کنید بخواهیم میانگین سال های جدا شده از تاریخ را بگیریم؛ اگر با استفاده از تابع Average میانگین سلول های C3 تا C16 را بگیریم مطابق تصویر زیر با ارور VALUE# مواجه می شویم. زیرا خروجی تابع های LEFT، RIGHT و MID متن هستند نه عدد.

 

 

 

در واقع سال های جدا شده از تاریخ فقط ظاهر عددی دارند اما از نظر اکسل متن هستند. پس باید با استفاده از تابع Value خروجی فرمول نوشته شده برای جداسازی روز و ماه و سال از تاریخ شمسی را به عدد تبدیل کنیم: در زیر نمونه این کار برای قسمت سال را می بینید:

 

 

2) اگر جداکننده روز و ماه و سال تاریخ به جای اسلش (“/”)، خط تیره (“-“) باشد، فرمول های نوشته شده در راهکار قبلی با ارور مواجه می شود (دلیل آن را خودتان بررسی کنید). اما توابع افزونه تقویم و تاریخ شمسی در اکسل پویش پایدار کماکان صحیح عمل می کنند.

 

 

3) همچنین اگر فرمت تاریخ را برعکس و به صورت راست به چپ وارد کنیم (مانند سلول B3 در تصویر زیر)، هیچ یک از فرمول های راهکار اول صحیح عمل نمی کند اما توابع افزونه بدون هیچ مشکلی کار می کنند.

 

 

لذا پیشنهاد می شود اگر در فایل های خود با تاریخ های شمسی سر و کار دارید، حتماً از امکانات بی نظیر افزونه تقویم و تاریخ شمسی در اکسل پویش پایدار استفاده کنید.

 

ممنون از توجه شما 🙂

هر گونه سوال، پیشنهاد یا انتقادی در رابطه با دوره رایگان آموزش اکسل دارید در بخش دیدگاه‌ها مطرح نمایید تا بتوانیم بهترین آموزش ها را برای شما علاقمندان به آموزش اکسل تهیه کنیم.