تبدیل تاریخ میلادی به شمسی در اکسل — راهنمای کاملاً کاربردی

از اکسل می‌توان برای زمان‌بندی و ایجاد تقویم کاری استفاده کرد. ولی در اکثر مواقع، ایجاد و به کارگیری تقویم شمسی و تبدیل تاریخ میلادی به شمسی در اکسل کار سختی محسوب شده و بسیاری از محاسبات دچار مشکل می‌شوند. در این متن از مجله فرادرس قصد داریم با استفاده از چند روش، تبدیل تاریخ میلادی را به تاریخ شمسی اجرا کنیم. البته باید توجه داشت که این تبدیل باید توانایی انجام محاسبات روی تاریخ را داشته باشد و اکسل با آن به عنوان یک عدد محاسباتی برخورد کند. در غیر انصورت مقداری که به صورت تاریخ شمسی در سلول‌های اکسل نوشته شده باشد، به صورت متن در نظر گرفته شده و توانایی انجام عملیات ریاضی روی آن‌ها مقدور نیست.

برای آشنایی بیشتر با نحوه فرمول نویسی و همچنین به کار بردن ماکروها در اکسل بهتر است مطالب دیگر مجله فرادرس با عنوان فرمول نویسی در اکسل — به زبان ساده و برنامه نویسی VBA در اکسل را مطالعه کنید. البته خواندن نوشتارهای ساخت تقویم شمسی در اکسل — راهنمای گام به گام و تبدیل تاریخ شمسی به میلادی در اکسل — راهنمای کاربردی نیز خالی از لطف نیست.

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

اغلب کاربران حرفه‌ای اکسل را برای برنامه‌ریزی و ثبت تاریخ‌های کاری مورد استفاده قرار می‌دهند. برای مثال اگر بخواهیم از روی تاریخ‌های حضور یک فرد در شرکت، تعداد روزهای کاری را محاسبه کنیم، یا برای برنامه‌ریزی یک پروژه، احتیاج به تعداد ماه‌های اجرای پروژه داشته باشیم، تعداد روزها یا ماه‌ها براساس تاریخ باید قابل محاسبه باشد.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

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

روش‌های تبدیل تاریخ میلادی به شمسی در اکسل

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

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

کلیک کنید

تبدیل تاریخ میلادی به شمسی در اکسل به کمک قالب‌بندی سلول

شاید ساده‌ترین کار برای تغییر تاریخ میلادی در اکسل و تبدیل به تاریخ شمسی، استفاده از قالب بندی سلول باشد. البته به یاد داشته باشید که این کار در نسخه‌های جدید اکسل (۲۰۱۶ یا ۲۰۱۹) قابل اجرا است. در نسخه‌های قدیمی، این امکان به طور پیش‌فرض بدون استفاده از ابزارهای برنامه‌نویسی شده، وجود ندارد.

البته اغلب به کمک نوشتن تاریخ شمسی به صورت یک عبارت متن (مثلا ۱/۴/۱۳۹۹) سعی می‌کنند که تاریخ شمسی را درون سلول ثبت کنند ولی باید توجه داشت که این شکل از ثبت تاریخ، باعث می‌شود که محاسبات ریاضی (مثلا بدست آوردن تعداد روزها بین دو تاریخ) امکان‌پذیر نباشد. از طرفی اگر تاریخ شمسی را به صورت ۱/۴/۹۹ بنویسیم، اکسل آن را مربوط به سال ۱۹۹۹ میلادی در نظر گرفته و مشخص است که باز هم محاسبات (مثلا بدست آوردن تاریخ ۱۰ روز آینده) به درستی انجام نخواهد شد و ممکن است با توجه به اختلاف روز‌ها در ماه‌های شمسی و میلادی، به تاریخ درستی دسترسی پیدا نکنید.

کاربرگ زیر را در اکسل در نظر بگیرید. ستون A شامل مقادیری است که به صورت تاریخ میلادی ثبت شده‌اند. می‌خواهیم معادل شمسی هر یک از این تاریخ‌ها را در ستون دیگری مانند B نمایش دهیم.

نکته: به یاد داشته باشید که اکسل برای نمایش تاریخ از قالبی که در پیکربندی ویندوز تعیین شده، استفاده می‌کند. همانطور که در تصویر بالا می‌بینید، اولین ارقام از سمت چپ، ماه و رقم‌های میانی، روز و در سمت راست تاریخ میلادی، سال مشخص شده است.

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

حال کافی است به کمک فرمول B1: = A1 مقدار سلول‌های ستون A را در ستون B کپی کنیم. از آنجایی می‌خواهیم قالب‌بندی ستون B تغییر نکند، از فرمول‌نویسی کمک گرفته‌ایم. البته به جای فرمول‌نویسی برای ایجاد ستون B، می‌توانستیم ابتدا همه مقادیر تاریخ را کپی کرده، سپس قالب‌بندی را تغییر دهیم.

به این ترتیب تمامی تاریخ‌های نوشته شده در ستون A به صورت تاریخ شمسی در ستون B ظاهر می‌شوند. جالب است که انجام هر گونه محاسبه یا به کارگیری توابع تاریخی روی ستون B، امکان‌پذیر است و کاملا با جنبه محتویات تقویمی میلادی مطابقت دارد.

اگر می‌خواهید قالب‌بندی تاریخ درون این سلول را به صورت کدهای مناسب ایجاد کنید، روند زیر را طی کنید.

• سلول یا سلول‌های مورد نظرتان را انتخاب کنید.
• پنجره قالب‌بندی سلول را به کمک کلید میانبر ترکیبی با فشردن کلیدهای همزمان Ctrl+1 ظاهر کنید.
• گزینه Custom را از کادر سمت راست (Category) انتخاب کرده و در بخش سمت راست، در کادر Type کد زیر را وارد کرده، سپس دکمه OK را کلیک کنید.

[$-fa-IR,16]dd/mm/yyyy;@

نکته: عبارت fa-IR,16 کدی است که برای نمایش تاریخ به صورت شمسی در سیستم عامل ویندوز ۱۰ به کار می‌رود و بخش دوم نیز قالب‌بندی نمایش تاریخ به شکل، ابتدا و در سمت راست، «سال»، در وسط «ماه» و سپس در سمت چپ، «روز»، را تعیین می‌کند. بنابراین کد به شکل dd/mm/yyyy به کار رفته است. البته می‌توانید این ترتیب را به دلخواه خود تغییر دهید. اگر به جای حروف dd از عبارت ddd استفاده کنید، نام روز هفته (مثلا چهارشنبه) در کنار تاریخ قرار می‌گیرد. همچنین کدی که به صورت mmm نوشته شود نام ماه (مثلا اردیبهشت) را درون سلول نمایش می‌دهد.

تبدیل تاریخ میلادی به شمسی به کمک کدنویسی VBA

این بار با استفاده از کدنویسی و به کارگیری زبان VBA در اکسل، تبدیل تاریخ را انجام می‌دهیم. به این منظور یک تابع با نام m2s ساخته شده که پارامترهای آن، یک سلول با تاریخ میلادی (با قالب متنی) است.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

کدی که در ادامه مشاهده می‌کنید به این منظور تهیه شده است.

1Function m2s(myDate, Optional Format = 0)
2  iDay = Day(myDate)
3  iMonth = Month(myDate)
4  iYear = Year(myDate)
5  iWeekday = Weekday(myDate)
6      
7  jdn = civil_jdn(iYear, iMonth, iDay)
8  Call jdn_persian(jdn, iYear, iMonth, iDay)
9  
10  Select Case iWeekday
11  Case 1
12    Rooz = ChrW(1740) & ChrW(1705) & ChrW(1588) & ChrW(1606) & ChrW(1576) & ChrW(1607)
13  Case 2
14    Rooz = ChrW(1583) & ChrW(1608) & ChrW(1588) & ChrW(1606) & ChrW(1576) & ChrW(1607)
15  Case 3
16    Rooz = ChrW(1587) & ChrW(1607) & ChrW(8204) & ChrW(1588) & ChrW(1606) & ChrW(1576) & ChrW(1607)
17  Case 4
18    Rooz = ChrW(1670) & ChrW(1607) & ChrW(1575) & ChrW(1585) & ChrW(1588) & ChrW(1606) & ChrW(1576) & ChrW(1607)
19  Case 5
20    Rooz = ChrW(1662) & ChrW(1606) & ChrW(1580) & ChrW(8204) & ChrW(1588) & ChrW(1606) & ChrW(1576) & ChrW(1607)
21  Case 6
22    Rooz = ChrW(1580) & ChrW(1605) & ChrW(1593) & ChrW(1607)
23  Case 7
24    Rooz = ChrW(1588) & ChrW(1606) & ChrW(1576) & ChrW(1607)
25  End Select
26  
27  
28  Select Case iMonth
29  
30  Case 1
31    mah = ChrW(1601) & ChrW(1585) & ChrW(1608) & ChrW(1585) & ChrW(1583) & ChrW(1740) & ChrW(1606)
32  Case 2
33    mah = ChrW(1575) & ChrW(1585) & ChrW(1583) & ChrW(1740) & ChrW(1576) & ChrW(1607) & ChrW(1588) & ChrW(1578)
34  Case 3
35    mah = ChrW(1582) & ChrW(1585) & ChrW(1583) & ChrW(1575) & ChrW(1583)
36  Case 4
37    mah = ChrW(1578) & ChrW(1740) & ChrW(1585)
38  Case 5
39    mah = ChrW(1605) & ChrW(1585) & ChrW(1583) & ChrW(1575) & ChrW(1583)
40  Case 6
41    mah = ChrW(1588) & ChrW(1607) & ChrW(1585) & ChrW(1740) & ChrW(1608) & ChrW(1585)
42  Case 7
43    mah = ChrW(1605) & ChrW(1607) & ChrW(1585)
44  Case 8
45    mah = ChrW(1570) & ChrW(1576) & ChrW(1575) & ChrW(1606)
46  Case 9
47    mah = ChrW(1570) & ChrW(1584) & ChrW(1585)
48  Case 10
49    mah = ChrW(1583) & ChrW(1740)
50  Case 11
51    mah = ChrW(1576) & ChrW(1607) & ChrW(1605) & ChrW(1606)
52  Case 12
53    mah = ChrW(1575) & ChrW(1587) & ChrW(1601) & ChrW(1606) & ChrW(1583)
54    
55  End Select
56
57  Select Case Format
58  
59  Case 1
60      m2s = Rooz & "," & iYear & "/" & iMonth & "/" & iDay
61  Case 2
62    m2s = Rooz & " " & horofi(iDay) & " " & mah & " " & ChrW(1605) & ChrW(1575) & ChrW(1607) & " " & horofi(iYear)
63  Case 3
64    m2s = iYear
65  Case 4
66    m2s = iMonth
67  Case 5
68    m2s = iDay
69  Case 0
70    m2s = iYear & "/" & iMonth & "/" & iDay
71  Case Else
72    m2s = "Only one of this number as format(0,1,2,3,4,5) for help visit http://sakhteman.wordpress.com"
73  End Select
74  
75End Function

برای مثال اگر فرمول زیر را به کمک تابع m2s به کار ببرید، تاریخ شمسی متناظر با تاریخ ۲۱ می ۲۰۲۱ بدست خواهید آورد. این تاریخ مصادف است با ۳۱ اردیبهشت ۱۴۰۰.

=m2s("5/21/2021")

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

در تصویر بالا، پس از اینکه دکمه Go را کلیک کردید، در پنجره بعدی، با دکمه Browse محل فایل FarsiTools.xla را مشخص کنید.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – تکمیلی در فرادرس

کلیک کنید

به این ترتیب این افزونه به لیست ابزارهای شما اضافه شده و توابع مربوطه نیز در بخش User Defined (توابع دست‌ساز کاربر) قابل دسترس هستند. کافی است از نوار فرمول (Formula bar) دکمه fx را کلیک کنید تا پنجره‌ای به مانند زیر ظاهر شود.

نکته: توجه داشته باشید که اگر تاریخ شمسی را بوسیله این توابع درون سلول ایجاد کرده‌اید، قالب‌بندی (Format Cell) و تبدیل تاریخ میلادی به شمسی در اکسل که در قسمت قبل بیان شده، تغییری در ظاهر سلول ایجاد نمی‌کند و شیوه نمایش محتویات، توسط فرمول و کد VBA تعیین می‌شود.

روش‌های تبدیل تاریخ شمسی به میلادی در اکسل

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

تبدیل تاریخ شمسی به میلادی در اکسل به کمک قالب‌بندی سلول

مشخص است که اگر تاریخی به صورت شمسی در کاربرگ اکسل ثبت شده باشد، به راحتی با تغییر قالب‌بندی می‌توان آن را به تاریخ میلادی تغییر داد. کافی است یکی از روش‌های استاندارد تاریخ را از پنجره Format Cell‌ و برگه Number در لیست Date انتخاب کنید. البته توجه داشته باشید که در بخش Calendar Type یکی از گزینه‌های Gregorian یا English را انتخاب کرده باشید. البته به قالبی که برای نمایش (ترتیب سال، ماه و روز) وجود دارد نیز دقت کنید تا تاریخ مورد نظرتان مطابق با سلیقه شما نمایش داده شود.

تبدیل تاریخ شمسی به میلادی در اکسل به کمک کد نویسی VBA

کد تبدیل تاریخ شمسی به میلادی را در ادامه مشاهده می‌کنید. البته توجه داشته باشید که هم در این کد و کد تبدیل تاریخ میلادی به شمسی، توابع دیگری به کار رفته‌اند که درون افزونه ثبت شده‌اند. در نتیجه فقط کپی کردن این کد درون پنجره VBA در اکسل نمی‌تواند عمل مورد نظرتان را اجرا کند. به عنوان مثال تابع persian_jdn و civil_jdn از این گونه توابع هستند که در هر دو کد به کار رفته‌اند. برای مشاهده این دو کد باید از محیط ویرایشگر VBA اکسل، استفاده کرده و برنامه‌ها را مرور کنید.

1Function s2m(myDate)
2  Dim iYear, iMonth, iDay
3  For I = 1 To Len(myDate)
4    st = Mid(myDate, I, 1)
5    
6    If (st = "/" Or st = "-" Or st = "." Or st = "\") And x <> 1 Then
7        x = 1
8        If I = 3 Then S = "13" & S
9    Else
10        If (st = "/" Or st = "-" Or st = "." Or st = "\") And x = 1 Then
11          x = 2
12          If I = 5 Or I = 7 Then S = Left(S, Len(S) - 1) & "0" & Right(S, 1)
13        Else
14          S = S & st
15        End If
16    End If
17   Next
18    If Len(S) = 7 Then S = Left(S, 6) & "0" & Right(S, 1)
19  myDate = S
20  iYear = Mid(myDate, 1, 4)
21  iMonth = Mid(myDate, 5, 2)
22  iDay = Mid(myDate, 7, 2)
23  If (iDay > 30 And iMonth > 6) Or iDay > 31 Or iMonth > 12 Then s2m = "Error": Exit Function
24  
25  If (iDay = 30 And iMonth = 12) Then
26  iYear1 = iYear + 1
27  iMonth1 = 1
28  iDay1 = 1
29  jdn1 = persian_jdn(iYear1, iMonth1, iDay1)
30  jdn = persian_jdn(iYear, iMonth, iDay)
31  If jdn1 = jdn Then s2m = "Error": Exit Function
32  End If
33  
34  jdn = persian_jdn(iYear, iMonth, iDay)
35  Call jdn_civil(jdn, iYear, iMonth, iDay)
36  s2m = iYear & "/" & iMonth & "/" & iDay
37End Function

به عنوان مثال فرمول زیر تاریخ شمسی را به میلادی متناظر آن تبدیل می‌کند.

=s2m("1387/6/19")

توجه دارید که تاریخ شمسی به صورت یک عبارت متنی درون گیومه ("") برای این تابع معرفی شده است. در تصویر زیر نمونه‌ای از کاربردهای دو تابع s2m و m2s همچنین horofi را مشاهده می‌کنید.

نکته: کدهای نوشته شده در این افزونه متعلق به سایت دیگری غیر از فرادرس است ولی می‌توانید آن را به صورت رایگان به همراه ابزارهای دیگر تبدیل تاریخ میلادی به شمسی در اکسل با کلیک کردن روی لینک (+) دریافت کنید.

همانطور که در این متن از مجله فرادرس خواندید، محتویات سلول‌های عددی با توجه به قالب بندی، می‌تواند به صورت تاریخ شمسی یا میلادی ثبت شود. برای ما مهم است که بتوانیم از مقادیر ثبت شده به صورتی استفاده کنیم که قابلیت اجرای عملیات ریاضی وجود داشته باشد. برای مثال ممکن است به تاریخ ۱۰ روز بعد از تاریخ جاری علاقمند باشیم یا بخواهیم بدانیم چنین روزی، چند شنبه است. در این متن به بررسی توابع و ابزارهای مرتبط با تبدیل تاریخ میلادی به شمسی در اکسل پرداختیم. همانطور که مطالعه کردید، در نسخه جدید اکسل ۲۰۱۹، توابع و قالب‌بندی مناسب برای تبدیل یا نمایش تاریخ شمسی وجود دارد که کار را برای بسیار از کاربران ساده خواهد کرد. همچنین توانایی انجام محاسبات روی تاریخ نیز بوجود خواهد آمد.

توجه داشته باشید که استفاده از قالب‌بندی برای تبدیل تاریخ میلادی به شمسی فقط مختص اکسل ۲۰۱۶ و ۲۰۱۹ است ولی برای نسخه‌های قدیمی باید از افزونه‌ها برای این کار استفاده کرد. البته شاید فرمول نویسی و به کارگیری محاسباتی که سال، ماه و روز میلادی را به سال شمسی تبدیل می‌کند، نیز مفید باشد.