برای تبدیل تاریخ در اکسل و تبدیل تاریخ میلادی به شمسی چندین راه مختلف و ساده ای وجود دارد که بستگی به ورژن اکسل و نوع تاریخ وارد شده در آن را دارد که در ادامه میخواهیم روش های مختلف را بررسی کنیم. البته برای درک بهتر مفهوم تاریخ در اکسل پیشنهاد می کنیم حتما تاریخ شمسی در اکسل را مطالعه بفرمائید.
اگر از آفیس 2016 به بالا و ویندوز 10 یا 11 استفاده کنید که روش کار بسیار ساده است :
کافیست محدوده تاریخ میلادی را انتخاب کنید
کلیدهای Ctrl + 1 را بزنید تا فرمت سلز باز شود .
حالا به تب Number رفته و Custom را انتخاب کنید.
عبارت زیر را آنجا می نویسیم تبدیل تاریخ در اکسل به راحتی انجام می شود.
[$-fa-IR,96]yyy/mm/dd
فهرست محتوا
تبدیل تاریخ میلادی به شمسی در اکسل
برای اینکه در اکسل بتوانید تاریخ میلادی را به هجری شمسی تبدیل کنید باید از تابع To_Hejri استفاده نمائید.
=To_Hejri(Now())=> تاریخ جاری میلادی را به شمسی تبدیل می کند 1399/08/24 =NoSlash(To_Hejri(Now())) => تاریخ میلادی جاری سیستم را به شمسی تبدیل می کند 13990824 =DayWeek(NoSlash(To_Hejri(A5)))&" "&Matni(NoSlash(To_Hejri(A5)))=> نتیجه در تصویر بالا =DayWeek(NoSlash(To_Hejri(A4)))& " "&Rooz(NoSlash(To_Hejri(A4))) &" "& MahName(mah(NoSlash(To_Hejri(A4)))) & " " &sal(NoSlash(To_Hejri(A4)))
تبدیل تاریخ شمسی به میلادی
برای اینکه بتوانید در اکسل تاریخ شمسی را به میلادی تبدیل کنید باید از تابع ShamsiToMiladi به شکل زیر استفاده کنید.
همانطور که در مثال زیر می بینید تابع ShamsiToMiladi دارای 3 آرگومان می باشد .
- آرگومان اول عدد 4 رقمی میخواهد که سال را نشان می دهد
- آرگومان دوم نشان دهنده شماره ماه شمسی خواهد بود .
- آرگومان سوم یک عدد بین 1 تا 31 که نشان دهنده روز تاریخ شمسی است .
=ShamsiToMiladi(sal(C2),mah(C2),Rooz(C2))=> 2020/11/14 =ShamsiToMiladi(LEFT(C3,4),MID(C3,6,2),RIGHT(C3,2))=> 2020/11/14
خط اول مثال بالا برای زمانی است که تاریخ در C2 نوشته شده باشد و به شکل 13990824 باشد یعنی اسلش نداشته باشد . این هم روشی ساده برای تبدیل تاریخ در اکسل می باشد.
خط دوم برای زمانی است که تاریخ به همراه اسلش وارد شده باشد.
تبدیل تاریخ در اکسل – شمسی به قمری
برای اینکه بتوانید تاریخ شمسی را به قمری تبدیل کنید باید از تابع ShamsiToGHamari به شکل زیر استفاده کنید.
این تابع دقیقا مشابه تابع قبلی عمل می کند و باید سه آرگومان اجباری که نشان دهنده سال و ماه و روز تاریخ شمسی هستند را به آن بدهید تا آن را به تاریخ قمری تبدیل کند ، که در تصویر و مثال زیر می بینید.
=ShamsiToGhamari(sal(C2),mah(C2),Rooz(C2))=> 1442/03/28 =ShamsiToGhamari(LEFT(C3,4),MID(C3,6,2),RIGHT(C3,2))=> 1442/03/28
تبدیل تاریخ قمری به شمسی و میلادی
برای تبدیل تاریخ قمری به شمسی و میلادی کافیست از توابع GhamariTiShamsi و تابع GhamariToMiladi به شکل زیر بهره ببرید.
=GhamariToShamsi(sal(C2),mah(C2),Rooz(C2))=> 1399/08/24 =GhamariToMiladi(LEFT(C3,4),MID(C3,6,2),RIGHT(C3,2))=> 2020/11/14
تبدیل تاریخ شمسی به میلادی و برعکس در ورژنهای پائین تر
فرض می کنیم که آفیس 2016 به بالا نداریم و همچنان نمی خواهیم از افزونه تاریخ شمسی در اکسل استفاده کنیم ، آیا راهی برای تبدیل تاریخ میلادی به شمسی وجود دارد؟ بله در این صورت باید از ترکیب توابع استفاده کنید که در ادامه فرمول آن را مشاهده خواهید کرد.
بدیهی است که به جای F3 در فرمول زیر آدرس مبوط به فایل خودتان را باید قرار دهید .
از این روش زمانی استفاده کنید که بخواهید محدوده کمی را تبدیل کنید و همچنین محدودیت افزودن ماژولها را دارید ، زیرا این روش در صورتی که داده های زیادی داشته باشید باعث کندی خواهد شد .
=CONCATENATE(INT((F3-7385)/365.25+1299),"/",IF(MOD(IF(INT(MOD((F3-7385)*100,36525)/100)<186,INT(INT(MOD((F3-7385)*100,36525)/100)/31),IF(MOD(INT((F3-7385)/365.25),4)=0,INT((INT(MOD((F3-7385)*100,36525)/100)-186)/30)+6,IF(INT(MOD((F3-7385)*100,36525)/100)<336,INT((INT(MOD((F3-7385)*100,36525)/100)-186)/30)+6,INT((INT(MOD((F3-7385)*100,36525)/100)-336)/29)+11))),12)+1<10,CONCATENATE(0,MOD(IF(INT(MOD((F3-7385)*100,36525)/100)<186,INT(INT(MOD((F3-7385)*100,36525)/100)/31),IF(MOD(INT((F3-7385)/365.25),4)=0,INT((INT(MOD((F3-7385)*100,36525)/100)-186)/30)+6,IF(INT(MOD((F3-7385)*100,36525)/100)<336,INT((INT(MOD((F3-7385)*100,36525)/100)-186)/30)+6,INT((INT(MOD((F3-7385)*100,36525)/100)-336)/29)+11))),12)+1),MOD(IF(INT(MOD((F3-7385)*100,36525)/100)<186,INT(INT(MOD((F3-7385)*100,36525)/100)/31),IF(MOD(INT((F3-7385)/365.25),4)=0,INT((INT(MOD((F3-7385)*100,36525)/100)-186)/30)+6,IF(INT(MOD((F3-7385)*100,36525)/100)<336,INT((INT(MOD((F3-7385)*100,36525)/100)-186)/30)+6,INT((INT(MOD((F3-7385)*100,36525)/100)-336)/29)+11))),12)+1),"/",IF(IF(INT(MOD((F3-7385)*100,36525)/100)<186,MOD(INT(MOD((F3-7385)*100,36525)/100),31)+1,IF(MOD(INT((F3-7385)/365.25),4)=0,MOD(INT(MOD((F3-7385)*100,36525)/100)-186,30)+1,IF(INT(MOD((F3-7385)*100,36525)/100)<336,MOD(INT(MOD((F3-7385)*100,36525)/100)-186,30)+1,MOD(INT(MOD((F3-7385)*100,36525)/100)-336,29)+1)))<10,CONCATENATE(0,IF(INT(MOD((F3-7385)*100,36525)/100)<186,MOD(INT(MOD((F3-7385)*100,36525)/100),31)+1,IF(MOD(INT((F3-7385)/365.25),4)=0,MOD(INT(MOD((F3-7385)*100,36525)/100)-186,30)+1,IF(INT(MOD((F3-7385)*100,36525)/100)<336,MOD(INT(MOD((F3-7385)*100,36525)/100)-186,30)+1,MOD(INT(MOD((F3-7385)*100,36525)/100)-336,29)+1)))),IF(INT(MOD((F3-7385)*100,36525)/100)<186,MOD(INT(MOD((F3-7385)*100,36525)/100),31)+1,IF(MOD(INT((F3-7385)/365.25),4)=0,MOD(INT(MOD((F3-7385)*100,36525)/100)-186,30)+1,IF(INT(MOD((F3-7385)*100,36525)/100)<336,MOD(INT(MOD((F3-7385)*100,36525)/100)-186,30)+1,MOD(INT(MOD((F3-7385)*100,36525)/100)-336,29)+1)))))
اگر هم تاریخ شمسی داشته باشید و به این شکل وارد شده باشد 1403/02/28 می توانید از فرمول زیر برای تبدیل تاریخ شمسی به تاریخ میلادی در اکسل استفاده کنید .
=IF(MOD(VALUE(LEFT(A1,4)),4)=0,(VALUE(LEFT(A1,4))-1)*365+(IF((VALUE(MID(A1,6,2))-1)<7,(VALUE(MID(A1,6,2))-1)*31,IF((VALUE(MID(A1,6,2))-1)>6,(VALUE(MID(A1,6,2))-1)*30+6)))+VALUE(RIGHT(A1,2))+INT((VALUE(LEFT(A1,4))-1)/4)+1,(VALUE(LEFT(A1,4))-1)*365+(IF((VALUE(MID(A1,6,2))-1)<7,(VALUE(MID(A1,6,2))-1)*31,IF((VALUE(MID(A1,6,2))-1)>6,(VALUE(MID(A1,6,2))-1)*30+6)))+VALUE(RIGHT(A1,2))+INT((VALUE(LEFT(A1,4))-1)/4))-466710
در فرمول بالا هم دقت داشته باشید که باید به جای A1 آدرس سلول خود را وارد نمایئد .
