تاریخ شمسی در اکسل | یکبار برای همیشه
تاریخ و زمان از اجزای اصلی و مهم و بعضا جدایی ناپذیر در گزارش ها و تحلیل ها بشمار می روند. از طرفی اکسل یکی از ابزارهای قوی در زمینه گزارشگیری به حساب می آید. پس محاسبات مربوط به تاریخ و زمان در این نرم افزار از اهمیت زیادی برخوردار است. به همین دلیل است که یک دسته از توابع اکسل به این موضوع یعنی Date & Time اختصاص داده شده است. اما موضوع مهمی که اکثر کاربران ایرانی با آن مواجه هستند نحوه انجام محاسبات تاریخ شمسی در اکسل و تبدیل تاریخ میلادی به شمسی و شمسی به میلادی است. برای تبدیل تاریخ در اکسل، از فایلی که در انتهای پست قرار داده شده استفاده کنید. در این فایل فرمولی قرار داده شده است که هم تاریخ میلادی به شمسی و هم تاریخ شمسی به میلادی قابل تبدیل هست. فقط کافیست فرمول رو در سلول مورد نظر کپی کنید و مرجع فرمول رو تغییر بدید.
برای انجام محاسبات بر روی تاریخ شمسی در اکسل چندین روش ارائه میکنم:
روش اول: برنامه نویسی VBA یا Add_Ins توابع شمسی
یکی از روش های کار با تاریخ های شمسی استفاده از زبان برنامه نویسی VBA است. می توان با استفاده از کدهای VBA، توابعی مشابه توابع موجود در اکسل تعریف کرد که بتواند همه محاسبات مشابه برای تاریخ های میلادی را روی تاریخ های شمسی اعمال کند. اگر زمان یا دانش کدنویسی ویژوال بیسیک نداشته باشیم، باید از افزونه های (Add_Ins) آماده که در این خصوص نوشته شده است استفاده کرد. با نصب این افزونه ها، یک سری توابع به توابع اکسل اضافه می شن که عموما در دسته بندی User Defined قرار می گیرند. این افزونه ها به همراه خود یک فایل راهنما دارند که توابع موجود در این افزونه ها را معرفی می کنند و نحوه عملکرد آنها را توضیح می دهند. با جستجو در این اینترنت، می تونید این افزونه ها را تهیه کنید.
جابجا کردن فایل های حاوی Add Ins باعث میشه که فایل عملکرد درستی نداشته باشه برای اینکه فایل در صورت انتقال هم با مشکلی در اجرا مواجه نشوند، مطابق مثال زیر (با استفاده از Drag & Drop) کدهای موجود در افزونه را به فایل اصلی انتقال دهید.
انتقال کدهای VBA به فایل برای تبدیل تاریخ میلادی به شمسی
روش دوم: نوشتن تاریخ بصورت عدد (بدون /)
در این روش با استفاده چند ستون کمکی و فرمول نویسی می تونیم محاسبات متنوعی رو روی تاریخ های شمسی انجام بدیم.
وجود / بین اعداد باعث میشه این اعداد به متن تبدیل شوند و خاصیت محاسبه و مقایسه را از دست بدهند. برای اینکه بتونیم براحتی محاسبه و مقایسه روی تاریخ های شمسی انجام بدیم، تاریخ رو بدون / و بصورت عدد ثبت میکنیم که این ویژگی (عدد بودن) حفظ شود. به عنوان مثال برای تاریخ ۱۳۹۷۱۲۰۴
با این کار محاسبات براحتی قابل انجام هستند اما ظاهر آن مثل کد هست و اصلا نشانگر تاریخ نیست. برای اینکه نمایش رو هم بصورت تاریخ در بیاریم باید از طریق فرمت سل تنظیم زیر رو انجام بدیم.
پس روی سل کلیک راست کرده و Format Cell رو انتخاب می کنیم. روی Custom کلیک میکنیم و مطابق شکل ۲ کد (۰۰۰۰”/”۰۰”/”۰۰) رو تایپ میکنیم و Ok می زنیم
شکل ۱- تاریخ شمسی در اکسل – تنظیم نمایش عدد به صورت تاریخ
بعد از زدن Ok، سلول مورد نظر رو بصورت ۱۳۹۷/۱۲/۰۴ می بینیم.
دقت داشته باشید که فرمت سل فقط نمایش سل را تغییر می دهد و محتوای سل همچنان همان عدد (با قابلیت مقایسه و محاسبه) است.
تشریح روش محاسبه
چون تاریخ ها بصورت عدد نوشته شده اند، به راحتی قابل مقایسه و محاسبه هستند. نحوه استفاده از این روش رو با یک مثال شرح میدم. مطابق شکل ۳ در یک شیت تاریخ های یکسال و وضعیت تعطیل یا کاری بودن و همچنین روزهای هفته رو را تایپ می کنیم.
شکل ۲- تاریخ شمسی در اکسل – شیت تنظیمات
حالا با استفاده از تابع Countifs محاسبات مربوط به این روش رو شرح میدیم:
سوال: فرض کنید میخواهیم ببینیم فاصله بین دو تاریخ ۹۶/۰۲/۰۴ و ۹۶/۱۰/۰۱ چند روز است؟
پاسخ: برای این کار، کافیست ببینیم بین این دو عدد در شیت Setting، چند عدد وجود دارد. پس از تابع Countifs استفاده میکنیم:
=Countifs(A1:A365,”<=961001″,A1:A365,”>=960204″)
در این تابع اعدادی که بزرگتر مساوی از ۹۶۰۲۰۴ و کوچکتر مساوی از ۹۶۱۰۰۱ هستند، شمرده می شوند.
سوال: همون سوال بالا رو با یک شرط بیشتر میخواهیم حل کنیم. مثلا می خواهیم تعداد جمعه های بین این دو تاریخ رو محاسبه کنیم:
پاسخ: کافیست یک شرط دیگر، جمعه بودن رو به تابع بالا اضافه کنیم:
=COUNTIFS(A1:A365,”<=۹۶۱۰۰۱″,A1:A365,”>=۹۶۰۲۰۴″,B1:B365,””جمعه)
این تابع اعدادی که بزرگتر مساوی از ۹۶۰۲۰۴ و کوچکتر مساوی از ۹۶۱۰۰۱ هستند به شرط اینکه سلول مجاور آنها در ستون B معادل کلمه جمعه باشد را شمارش می کند.
همینطور می تونید روزهای کاری و تعطیلات رسمی رو هم در محاسبات خودتون دخیل کنید.
حسن استفاده از این روش، انعطاف پذیری بسیار بالاست، چرا که اگر تقویم خاص با ویژگی های منحصر بفرد (مثلا روزهای خاص محل کار، روزهای تولد و …) داشته باشید، با این روش می تونید محاسبات دلخواه رو اعمال کنید.
افزونه تقویم شمسی در اکسل
افزونه بسیار کاربردی و هوشمند جهت درج و تبدیل تقویم شمسی
روش سوم: استفاده از توابع Date & Time و تبدیل جواب نهایی به تاریخ شمسی در اکسل
روش دیگر در کار کردن با تاریخ های شمسی استفاده از تاریخ های میلادی و همه توابع موجود در دسته توابع Date & Time و در نهایت تبدیل نتیجه به تاریخ شمسی است.
برای تبدیل تاریخ میلادی به شمسی (بدون VBA) باید از ترکیب توابع مختلفی از جمله توابع متنی، منطقی، ریاضی و … استفاده کرد. برای مشاهده نمونه ای از ترکیب این توابع در تبدیل تاریخ میلادی به شمسی، می تونید از فایلی (تبدیل تاریخ میلادی به شمسی و برعکس) که در انتهای آموزش قرار داده شده استفاده کنید.
این روش هم مانند سایر روش ها نقاط ضعف و قوتی داره. مثلا برای محاسبه روزهای تعطیل و کاری و … از این روش نمیشه استفاده کرد چرا که روزهای تعطیل تاریخ میلادی با شمسی متفاوت است. از این روش برای مقایسه تاریخ ها و محاسبه فاصله بین دو تاریخ و … میشه استفاده کرد.
استفاده ترکیبی از این روش ها می تونه کارایی زیادی داشته باشه. خوبه که به همه این روش ها و نقاط ضعف و قوت هر کدام تسلط داشته باشیم و با توجه به نیاز خودمون، از هر کدام در جای مناسب استفاده کنیم.
روش چهارم: استفاده از فرمت نمایش تاریخ شمسی در اکسل ۲۰۱۶
این گزینه که در نسخه اکسل ۲۰۱۶ اضافه شده به شما امکان تغییر ظاهر تاریخ های میلادی به شمسی را داره. توجه کنید ظاهر آن را تغییر میده یعنی در اصل تاریخ میلادی هست و توابع و محاسبات انجام شده روی این تاریخ بر اساس تاریخ میلادی انجام میشود. همانطور که در شکل ۳ میبینید تاریخ میلادی در سلول وارد شده و با تغییر فرمت تاریخ در فرمت سل به Persian، امکان نمایش تاریخ میلادی به صورت شمسی ایجاد شده.
شکل ۳- با استفاده از Format Cell در اکسل ۲۰۱۶
هنوز جا داره که مایکروسافت در این زمینه امکانات بیشتری برای این گزینه درنظر بگیره. در واقع تبدیل تاریخ میلادی به شمسی با این روش در ظاهر انجام میشه و عملا ماهیت اصلی تاریخ تغییر نمیکنه.
ویدئوی آموزشی کار با تاریخ شمسی در اکسل
سلام و درود
من میخام تاخیر ورود پرسنلم رو هر روز ثبت کنم و در نهایت با ساعت هایی که سبت به ساعت ۸ دیر اومدن و تاخیر شده بهم جمع بده
که جمع من بشه ۸ معادل یک روز کاری بی حقوقشون کنم ۱ روز
کسی میتونه کمکم کنه
درود بر شما
برای محاسبات مربوط به ساعت این پست رو بخونید
https://excelpedia.net/excel-time-calculation/
سلام؛
حاصل تفریق ساعت شروع و پایان رو به دقیقه با فرمول SUM(HOUR(AS1620-AT1620)*60;MINUTE(AS1620-AT1620)) نوشتم، اما یک مشکل دارم.
برای مواقعی که ساعت پایان کوچک تر از ساعت شروع هست Error میده.
مثلا شروع ۲۳:۵۰ و پایان ۰۰:۳۰
لطفا با یک روش ساده، راهنمایی کنید!!!!!
بسیار سپاس.
سلام
کافیه با تابع MAX ساعت بزرگتر رو پیدا کنید و با تابع MIN ساعت کوچکتر و حاصل این دو تابع رو از هم کم کنید.
ممنون از راهنمایی شما.
باید تفاوتش بشه ۴۰ دقیقه، با این روشی که فرمودید، میشه ۱۴۰۰.
شروع ۲۳:۵۰ بوده و پایان ۰۰:۳۰ روز بعدش
خواهش میکنم
با فرمول زیر من تست کردم دقیقا ۴۰ دقیقه میده:
(MAX(H22,H23)-MIN(H22,H23))*24*60
تو این فرمول فرض شده تو یکی از سلول ها H22 و H23 ساعت نوشته شده.
نکته ای که هست اینه که اگه شما میخواید مقدار دو ساعت که در بازه ۲۴ ساعت هست رو کم کنید مشکلی نداره که به صورت بالا در سلول بنویسید (مثلا ۰۰:۳۰ یا ۲۳:۵۰) اما وقتی دارید دو ساعت در روز های مختلف رو حساب میکنید دیگه باید با تاریخ زده بشه (مثلا ۱/۱/۱۹۰۰ ۱۲:۳۰:۰۰ AM ) که دقیقا محاسبه بشه.
سلام. ممنون می شم اگر بفرمایید چظور می شه تاریخ خورشیدی رو به میلادی تبدیل کرد در اکسل
سلام
در همین آموزشی که در بالا هست، یک فایل نمونه ارائه شده که فرمول تبدیل تاریخ ها به هم در اون موجود هست….
فایل اکسل رو دانلود کنید متوجه میشید
سلام
بنده یک فرمول برای محاسبه مدت زمان می خوام برای تاریخ شمسی برای مثال یک خودرو درتاریخ ۱۳۹۶/۱۰/۱۲ در ساعت ۰۹:۰۰ واردت تعمیرگاه می شود و در تاریخ ۱۳۹۶/۱۰/۱۵ در ساعت ۱۵:۳۰ از تعمیرگاه خارج می شود بنده می خواهم مدت زمان توقف این دستگاه رو بدست بیارم لطفاً اگر می توانید فرمولی در اکسل برای بنده طراحی کنید و هر چه هزینه آن بشود پرداخت خواهم کرد
ممنون
سلام
کافیه تاریخ شمسی رو با یکی از روش های یک یا سه به میلادی تبدیل کنید و بعد از تبدیل شدن به تاریخ میلادی به راحتی از هم کنید.
سلام ممنون بابت مطالب مفیدتون
من میخوام در یه سلول از اکسل تاریخ به روز رو داشته باشم
با تابع today به میلادی میده تاریخ رو – من شمسی میخوام بشه
به این روش که توی عکس نشون دادین” فورمت سلول ” خواستم با این روش انجام بده اما توی اکسل من گزینه persian خالی رو داره برای شما persian (iran) هست و گزینه پایینش calendar type = persian هست ولی برای من نداره این گزینه رو
میشه راهنماییم کنید
سلام
تشکر
نسخه اکسل باید ۲۰۱۶ باشه و روی ویندوز ۱۰ نصب شده باشه تا این گزینه نمایش داده بشه.
سلام
در روش countifs حتما باید شروط رو با عدد مشخص کنیم و نمیشه آدرس سلول رو بدیم؟
یعنی به جای شرط ”<=961001″ نمیشه بگیم "<=D5" ؟؟
سلام
چرا میشه
منتها اینطوری:
“<="&D5 سلول رو جدا وصل کنید به علامت بزرگتر
خب اگر شیت تاریخ با شیت محاسبات متفاوت باشه چی؟
یعنی من در شیت «تاریخ» بیام روزهای یک سال رو به ترتیب بذارم و در شیت «محاسبه» بیام دو تا روز (دو تا سلول) رو از هم کم کنم. چطور متوجه میشه سلول D5 متعلق به کدوم شیت هست؟ در واقع چطور به اکسل بگم برو در سلول های شیت «تاریخ»، سلول D5 از شیت «محاسبه» رو پیدا کن و مثلا بزرگ تر از اون رو محاسبه کن؟
ضمن اینکه فرمت سایتتون “” ها رو در ابتدا و آخر جملات به “ تبدیل میکنه که کپی کردن رو سخت میکنه؛ برای این هم تدبیری بیندیشید خیلی خوبه :-)
خب کافیه اسم شیت هم به سلول وصل باشه دیگه
یعنی اگه اسم شیت شما “محاسبه” هست، آدرس سلولتون میشه این:
یا اینکه از تابع Address استفاده کنید برای این موضوع یعنی:
خروجی این تابع میشه سلول D5 از شیت محاسبه
برای اون مورد هم چشم :). بررسی میکنیم
خیلی خیلی ممنونم
برای بازه هم همینطوری میشه آدرس دهی کرد؟
مثلا پشت A1:A365 بذاریم تقویم! درست میشه؟
متأسفانه این ریزه کاری ها همیشه باعث میشه یه جای کار بلنگه و آدرس دهی غلط بشه!
منطق آدرس دهی رو هم توضیح دادید در سایت؟ لطفا لینکش رو برام بذارید. ?
خواهش میکنم
امتحان کنید حتما نتیجه میگیرید.
الگو حتما باید رعایت بشه.
این لینک رو راجع به تابع Address بخونید….
https://excelpedia.net/address-function/
سلام. همچنین آیا میشه به جای “=۹۶۰۱۰۲
با سلام
پس از نصب لینک افزونه که گذاشتین پایین صفحه (دانلود افزونه و فایل تبدیل تاریخ میلادی به شمسی و برعکس) چیکار باید بکنیم تا تاریخ به شمسی تغییر کنه؟
توضیح بدین لطفا…
با تشکر
سلام
نحوه نصب افزونه رو از لینک زیر ببینید.
https://excelpedia.net/number-to-text/
بعد از نصب یک سری توابع اضافه میشه به اکسل که هر کدومش ی کاری انجام میده.
در مورد فایل هم که حاوی فرمول هست، کافیه سل مرجع رو ببینید و تاریخ مورد نظر رو در سلول مرجع بذارید تا تبدیل بشه
سلام
لطفا روش چهارم را توضیح بدهید چگونه انجام دهیم
ممنون
سلام
این قسمت از آموزش به روز شد.
در توضیحات حضرت عالی در آخر تو تا تاریخ از هم دیگر کسر نشد
بایستی دقیقا با راهنمای شما بتوان دو تا تاریه رو ازهم کسر کرد .
فقط فایل دانلود گذاشتید و در آخر هیچ من نفهمیدم نتیجه چی شد
چطور دو تاریخ رو کم کنم
شما فقط به فقط یک شیت خالی رو در نظر بگیر و در دو سلول هر کدام یک تاریخ نوشته شده (۹۰/۰۷/۱۵) و سلول دیگر ۹۵/۱۰/۰۱)
این دو سلول رو برایم منها کنید . همین . نحوه کار را توضیح دهید
سلام
فایل دانلود فقط فایل تبدیل تاریخ میلادی به شمسی و بالعکس هست
دوتاریخ شمسی رو به سه روش میتونید از هم کم کنید
یا از افزونه تاریخ های شمسی استفاده کنید
یا تاریخ شمسی رو به میلادی تبدیل کنید و از هم کم کنید
یا اینکه در یک شیت تاریخ یکسال رو بنویسید، مثل شکل ۲. بعد با countif افاصله رو پیدا کنید.
این سوال “سوال: فرض کنید میخواهیم ببینیم فاصله بین دو تاریخ ۹۶/۰۲/۰۴ و ۹۶/۱۰/۰۱ چند روز است؟”، دقیقا سوال شماست و جواب داده شده.
فقط باید دیتابیسی مشابه عکس ۲ تهیه کنید که بشه فاصله اونها رو تعیین کرد. فرمول countif مربوطه هم نشون داده شده
سلام.
مرسی از سایت بی نظیرتون.
من میخوام در سایت ثبت نام کنم ولی صفحه ثبت نام باز نمیشه و فقط یه جمله نوشته که “ورود با حساب کاربری سایت شما” دیگه هیچ چیز دیگه ای توی صفحه ثبت نام نیست. گمونم لینک خرابه. ممنون میشم پیگیری کنید.
بازم ممنون از مطالب فوق العاده سایت
سلام
خیلی ممنون
مشکل برطرف شد.
میتونید ثبت نام کنید.