تاریخ میلادی در اکسل
کار با داده های تاریخی در اکسل یکی از موارد پر کاربرد و مهم است که گاهی ممکن است مشکل ساز شود.در این مقاله با ما همراه باشید تا 9 ترفند جذاب و کاربردی در کار با داده های تاریخی را به شما آموزش دهیم.
1.اصلاح فرمت:
گاهی اوقات ممکن است که داده های تاریخی ما پس از ورود در جدول با فرمت عدد نمایش داده شوند.
ابتدا محدوده داده های خود را انتخاب کنید و سپس از تب home به گروه number دقت کنید ،در کادر مقابل فرمت داده ها نمایش داده میشود که در اینجا فرمت داده ی ما روی General قرار دارد!!
برای اصلاح این مشکل کادر مقابل را باز کنید در این کادر دو فرمت تاریخی short date/long date قرار داد که میتوانید یکی را انتخاب نمایید .
اگر فرمت دیگری مدنظر دارید میتوانید از پنجره ی فرمت سل از بخش Date که مخصوص داده های تاریخی است فرمت های دلخواه خود را انتخاب کنید
و یا از بخش custom فرمت سفارشی ایجاد کنید.
(برای اطلاعات بیشتر مقاله فرمت سل را مطالعه کنید)
تغییر فرمت با کلید میانبر:
همچنین روش دیگر که از محبوبیت بیشتری برخوردار است این است که محدوده ی داده هایمان را انتخاب کنیم سپس از با استفاده از کلید میانبر Ctrl + Shift + 3 داده های ما به شکل تاریخی خود نشان داده خواهند شد
*نکته*
برای تبدیل فرمت تاریخ به فرمت عدد کافی است کلید میانبر ~ + Ctrl + Shift + را بزنید
جالب است بدانید:
مبدا تاریخ در اکسل سال 1900 است و تاریخی که با فرمت عدد به شما نشان داده میشود در واقع تعداد روز های گذشته از 1 ژانویه 1900 است.
2. وارد کردن تاریخ روز جاری:
برای درج تاریخ روز به دو روش میتوان عمل کرد:
1.از کلید میانبر : + ctrl استفاده کنید
2.استفاده از تابع TODAY() : این تابع ، تاریخ روز جاری را در سیستم به شما نشان میدهد
3. محاسبه اختلاف دو تاریخ میلادی در اکسل:
در بسیاری از مواقع نیاز است که تعداد روز های بین دو تاریخ را شمارش کنیم اکسل این کار را راحت کرده و کافیست به شکل زیر عمل نمایییم:
تاریخ کوچکتر را از تاریخ بزرگتر کم کنیم به همین راحتی!
عدد به دست امده اختلاف بین این دو تاریخ است اگر میخواهید که تاریخ روز اول نیز حساب شود باید فرمول را به شکل زیر تغییر دهید:
تاریخ بزرگ – تاریخ کوچکتر+1
مثال:
قصد داریم تعداد روز های اقامت اقای سعیدی در هتل را بررسی کنیم
تاریخ ورود:19-aug-23 تاریخ خروج:12-sep23
با توجه به شکل مدت زمان اقامت اقای سعیدی در هتل 25 روز است.
*نکته*
روز ورود باید حساب شود به همین دلیل به علاوه ی 1 کردیم.
4.شمارش بین دو تاریخ میلادی:
برای محاسبه ی تفاوت ها بین دو تاریخ میتوان از تابع DATEDIF استفاده نمود. این تابع از توابع پنهان در اکسل است و برای استفاده از آن باید )Datedif= را تایپ کنید.
ساختار این تابع به شکل زیر است:
- تاریخ شروع:کوچکترین تاریخ بین این دو تاریخ است
- تاریخ پایان: تاریخ بزرگتر
- فاصله زمانی: تعداد روز های بین این دو تاریخ را به شما نشان میدهد. این فاصله زمانی حتما باید بین دبلکوتیشن (“”) قرار گیرد
فاصله زمانی بین دو تاریخ در 6 نوع میتوان مشخص کرد:
“d” : تعداد روز های بین دو تاریخ را نشان میدهد
“m” : تعداد ماه های بین دو تاریخ را نشان میدهد
“y” : تعداد سال های بین دو تاریخ را نشان میدهد
“md“: بدون در نظرگرفتن سال و ما تعداد روز را برمیگرداند
“ym“: بدون در نظر گرفتن سال تعداد ماه های بین دو تاریخ را برمیگرداند
“yd“: بدون در نظر گرفتن سال تعداد روز های بین دو تاریخ را برمیگرداند
5. هر ماه چندمین ماه از سال است؟
به کمک اکسل میتوانیم مشخص کنیم هر ماه چندمین ماه از سال میلادی است برای درک بهتر مثال زیر را مطالعه نمایید:
در شکل زیر لیستی از ماه های سال داریم کافی است مراحل زیر را دنبال کنیم.
1.ابتدا علامت مساوی را در یکی از سلول ها وارد نماییید
2.تابع MONTH را صدا بزنید و روی سلول حاوی ماه مورد نظر کلیک کنید برای مثال ماه (August)
3.عملگر & را وارد کرده و بعد از آن عدد 1 را بنویسید و در اخر اینتر بزنید
عدد نشان داده شده مشخص میکند که این ماه چندمین ماه سال است (August هشتمین ماه سال میلادی است)
6.شمارش هفته ی یک تاریخ با تابع Weeknum:
با استفاده از تابع weeknum در اکسل میتوان شماره هفته مربوط به یک تاریخ مشخص را تعیین کرد.به عبارتی مشخص کرد تاریخ موردنظر چندمین هفته در سال میلادی است.
برای این منظور کافی است پس از علامت مساوی در یک سلول تابع Weeknum را صدا زده و سپس روی تاریخ مورد نظر کلیک نمایید و در انتها از لیست ظاهر شده نوع گردش هفته را انتخاب کنید.عددی که این تاریخ به ما نشان میدهد عددی بین 1 تا 53 است.
ساختار این تابع به شکل زیر است:
سریال عددی:
این پارامتر اجباری است و تاریخ یا مقادیر مرتبط با ان را مشخص میکند و میتواند یک تاریخ مستقیم یا آدرس یک سلول حاوی تاریخ باشد.
نوع گردش هفته:
این پارامتر روز شروع هفته را مشخص میکند که میتوانید خودتان آن را از طریق کادر باز شده تنظیم کنید، در مثال قبل هر هفته از روز شنبه شروع میشد.
7.نمایش روز در کنار تاریخ:
برای نمایش روز در کنار تاریخ مورد نظرکافی است مرحل زیر را دنبال کنید:
- سلول حاوی تاریخ را انتخاب کنید
- کلید میانبر Ctrl + 1 را بزنید تا پنجره فرمت سل باز شود
- حالا در قسمت Type این کد را وارد کنید dddd dd-mmm-yy
مثال:
تاریخ: 15 دسامبر 2003
پس با توجه به شکل تاریخ مورد نظر روز دوشنبه است.
8.تاریخ میلادی و تابع EOMONTH:
با استفاده از این تابع EOMONTH در اکسل میتوان تاریخ اخرین روز ماه را بر اساس یک تاریخ مرجع به دست اورد در ادامه چند ترفند کاربردی در کار با این تابع به شما اموزش می دهیم.
این تابع دو ورودی دارد:
=EOMONTH(start_date, months)
تاریخ مرجع :
تاریخی که میخواهید بر اساس ان تاریخ اخرین روز ماه را بدست اورید
ماه ها :
تعدادماه هایی که میخواهید به جلو یا عقب بروید
تاریخ اخرین روز ماه:
=EOMONTH(A1,0)
*نکته*
اگر پس از پرانتز 1+ بنویسید تاریخ اخرین روز ماه به تاریخ اولین روز ماه بعد تبدیل میشود
=EOMONTH(A1,0)+1
تاریخ اولین روز ماه جاری را میتوان به شکل زیر با تابع EOMONTH به دست اورد
=EOMONTH(A1,-1)+1
اگر در ارگومان دوم عدد 1- بنویسیم تاریخ اخرین روز ماه قبل را به ما میدهد:
=EOMONTH(A2,-1)
9.سال کبیسه بعدی در تاریخ میلادی:
فرمول زیر فرمول نشان دادن سال کبیسه میباشد:
19-AUG-23
=MAX((MOD(YEAR(EDATE(A1,{0;12;24;36})),4)=0)*(YEAR(EDATE(A1,{0;12;24;36}))))
این فرمول بررسی میکند از 4سال آینده کدام سال بر 4 بخش پذیر است و اگر بر یکی بخش پذیر بود فرمول آن را نشان میدهد
برای مثال وقتی من فرمول را وارد کردم نشان داد که سال 2024 سال کبیسه خواهد بود
*نکته*
این فرمول همیشه سال کبیسه ی بعدی را نشان میدهد حتی اگر تاریخ فعلی ما سال کبیسه باشد .اگر میخواهید سال کبیسه ی کنونی در نظر گرفته شود فرمول را به شکل زیر تغییر دهید:
=MAX((MOD(YEAR(EDATE(A1,{0;12;24;36})),4)=0)*(YEAR(EDATE(A1,{0;12;24;36}))))
<<لطفا نظرات خود را با ما به اشتراک بگذارید>>
همچنین با ما همراه باشید: