آخرین به‌روزرسانی: 18 بهمن 1401

تاریخ‌ها و توابع تاریخ

سؤالاتی که در این فصل به آنها پاسخ داده می‌شود:

• وقتی‌که تاریخ را در اکسل وارد می‌کنم به‌جای 1/4/2003 عددی شبیه به 37625 دیده می شود. این عدد چه معنایی دارد و چگونه می‌توان آن را به تاریخ معمولی تغییر داد؟
• آیا فرمولی وجود دارد که توسط آن بتوان تاریخ امروز را به شکلی خودکار نمایش داد؟
• چگونه می‌توان تاریخی را که 50 روز کاری بعد از تاریخ دیگرمی باشد، مشخص کرد؟ اگر تمایل داشته باشیم روزهای تعطیل را از آن جدا کنیم چه باید انجام داد؟
• چگونه می‌توان تعداد روزهای بین دو تاریخ را مشخص کرد؟
• 500 تاریخ مختلف وارد شده در کاربرگ اکسلی وجود دارد. چگونه می‌توان فرمولی نوشت که از هریک از تاریخ‌ها ماه، سال، روز ماه و روز هفته را استخراج نماید؟
• سال، ماه و روز ماه از تاریخی در اختیارم قرار داده شده است. آیا راهی ساده وجود دارد که تاریخ واقعی را بازیابی کرد؟
• کسب و کار من در زمینه خریدوفروش اتومبیل است. درمواردی اطلاعاتی مربوط به تاریخ خرید وتاریخ فروش ماشین ها را ثبت کرده ام. آیا می‌توانم به‌راحتی تعیین کنم که ماشین‌ها را چند ماه نگه‌داشته ام؟
• چگونه می‌توان تاریخی ثابت (بدون تغییر) در یک کاربرگ اکسل قرار داد؟

برای به‌تصویرکشیدن یکی از پراستفاده‌ترین فرمت‌های ماه – روز – سال در مایکروسافت اکسل 2019 بیایید فرض کنیم امروز چهارم ژانویه سال 2004 است. می‌توانید این تاریخ را به شکل‌های زیر وارد کنید:

• 1/4/2004
• 4-Jan-2004
• January 4, 2004
• 1/4/04

اگر تنها از دو عدد برای نمایش سال استفاده کنید و عدد شما 30 و یا عددی بزرگ‌تر از آن باشد، اکسل این اعداد را نمایشگر سال‌هایی در قرن بیستم فرض می‌کند؛ اگر ارقام کمتر از 30 باشند اکسل آنها را نمایشگر سال‌هایی در قرن بیست و یکم فرض می‌کند. مثلاً1/1/29  به‌عنوان اول ژانویه سال 2029 در نظر گرفته می‌شود اما1/1/30  به‌عنوان تاریخ یک ژانویه سال 1930 در نظر گرفته می‌شود. هرسال، سال در نظر گرفته شده به‌عنوان تاریخ قرن بیستم یک سال افزایش پیدا می‌کند.

پاسخ به سؤالات این فصل

وقتی‌که تاریخ را در اکسل وارد می‌کنم به‌جای1/4/2003  عددی شبیه به 37625 دیده می شود. این عدد چه معنایی دارد و چگونه می‌توان آن را به تاریخ معمولی تغییر داد؟

گاهی اوقات روش کارکرد اکسل با تاریخ برای افراد تازه‌کار سردرگم کننده است. کلید رهایی از این سردرگمی درک این مطلب است که اکسل می‌تواند تاریخ را در اشکال مختلفی از ماه، روز و سال نمایش دهد و یا اینکه می‌تواند تاریخ را به شکل اعداد سریال  نمایش دهد. تاریخ به شکل اعداد سریال مثل عدد 37625 خیلی ساده عدد صحیح مثبتی است که نمایشگر روزهای میان تاریخ ارائه شده و یکم ژانویه سال 1900 می باشد. در محاسبه هم تاریخ یکم ژانویه سال 1900 و هم تاریخ حاضر در نظر گرفته شده‌اند. مثلاً اکسل تاریخ سوم ژانویه سال 1900 را به شکل عدد 3 نشان می‌دهد که بدان معنا است که تنها 3 روز میان تاریخ یکم ژانویه سال 1900 و سوم ژانویه 1900 وجود دارد (شامل هر دو تاریخ می‌شود)

نکته: اکسل فرض را بر آن می‌گیرد که سال 1900 سالی کبیسه و شامل 366 روز است. در واقع سال 1900 شامل 365 روز می‌باشد. برای کسب اطلاعات درباره منشأ این خطا (Bug) به آدرس زیر مراجعه کنید:

. www.joelonsoftware.com/items/2006/06/16.html

 تصویر 1-7 به شما کاربرگی بنام Serial Format در فایلی بنام Dates.xlsx نشان می‌دهد. فرض کنید به شما تاریخ‌هایی داده شده که در سلول‌های محدوده D5:D14 به شکل اعداد سریال نمایش داده شده‌اند. مثلاً عدد 37622 در سلولD5  تاریخی را که 37622 روز بعداز اول ژانویه سال 1900 است را مشخص کرده است.( شامل یکم ژانویه سال 1900 و روز 37622) برای نمایش این تاریخ سریال به فرمت ماه-روز- سال، آنها را در محدوده E5:E14 کپی کنید. محدوده سلول E5:E14 را انتخاب کرده، روی این انتخاب راست کلیک کنید و بعد گزینه Format Cells را انتخاب نمایید.( هروقت بخواهید می‌توانید کادر محاوره ای Format Cells‌را با فشاردادن کلیدهای Ctrl+L احضار کنید) حالا فرمت داده هایی را که دوست دارید از فهرست نشان داده شده در تصویر 2-7 انتخاب کنید. همان‌طور که در تصویر 1-7 می بینید، تاریخ‌های موجود در محدوده E5:E14 به فرمت تاریخ نمایش داده می شوند. اگر بخواهید فرمت تاریخ‌ها به شکل اعداد سریال باشد، سلول‌های E5:E14 را انتخاب کنید، سپس روی محدوده انتخاب راست کلیک کرده و گزینه  Format Cells ‌و سپس از میان فرمت های ارائه شده گزینه General  را انتخاب کنید.

تصویر 1-7 استفاده از دستورFormat Cells برای تغییر داده ها از حالت اعداد سریال به شکل ماه- روز -سال

تصویر 2-7 فهرستی از فرمت‌های تاریخ در کادر محاوره‌ای Format Cells

 تغییر فرمت تاریخ یک سلول به حالتGeneral به سادگی فرمت اعداد سریال داده ها را باز می‌گرداند. راه دیگری برای تغییر حالت داده ها به شکل اعداد سریال استفاده از تابعDATEVALUE و قراردادن تاریخ در میان دو گیومه می‌باشد. مثلاً در کاربرگ Date Format در فایلی بنام Dates.xlsx ، سلول I5 حاوی فرمول DATEVALUE(“2003/4/1”) می‌باشد. در اینجا اکسل عدد سریال 37625  را بدست می آورد که عدد سریال تاریخ چهارم ژانویه سال 2003 می‌باشد.

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

نمایش تاریخ جاری با فرمول بسیار ساده است. همان‌طور که با مشاهده سلولC13 از کتاب کار Date Format از فایلی بنام Dates.xlsx در تصویر 3-7  می بینید، وارد کردن تابعTODAY()

در سلول باعث نمایش دادن تاریخ جاری می‌شود. این تصویر در تاریخ 11 ژوئن سال 2018  تهیه شده است.

تصویر 3-7 مثال‌هایی از توابع تاریخ

چگونه می‌توان تاریخی را که 50 روز کاری بعد از تاریخ دیگرمی باشد را مشخص کرد؟ اگر تمایل داشته باشیم روزهای تعطیل را از آن جدا کنیم چه باید انجام داد؟

تابعWORKDAY(start_date,#days,[holidays]) تاریخی را که تعداد روزهای کاری آن  با آرگومان#days بعد از تاریخ شروعی معین ،مشخص شده است را نشان می دهد.( روز کاری روزی غیر از تعطیلات آخر هفته است) Holidays ( تعطیلات) آرگومان انتخابی ای برای این تابع است که به شما اجازه می دهد از محاسبه خود هر تاریخی را که در محدوده سلول درج شده ، را حذف نمایید. بنابراین وارد کردن فرمولWORKDAY(C14,50) در سلولD14 در کاربرگ Date Format به ما می گوید که 3/14/2003  پنجاه روز کاری بعد از تاریخ 01/03/2003 می‌باشد. توجه کنید که به‌جای ارجاع به تعطیلات در سلول‌های دیگر، می‌توانید آنها را به شکل عدد سریال هر روز از تعطیلات در فرمول WORKDAY در میان {} قرار دهید. مثلاً فرمول:

WORKDAY(38500,10,{38600,38680,38711}) دهمین روز کاری بعد از تاریخی با شماره سریال 38500 را با نادیده‌گرفتن روزهای تعطیل روز کارگر، عید شکرگزاری و روز کریسمس سال 2005 پیدا می‌کند.

تابعWORKDAY.INTL در نرم‌افزار اکسل 2010 معرفی شد. این تابع به شما اجازه می‌دهد تا مفهوم دقیق موردنظر خود را از عبارت روز کاری مشخص نمایید. دستور زبان این تابع عبارت است ازWORKDAY.INTL(start_date,days,weekend, [holidays]). آرگومان سوم شما را قادر می سازد تا روز تعطیل را تعریف نمایید. شما می‌توانید از کدهای زیر برای تعریف کردن روز تعطیل استفاده نمایید.

مثلاً درکاربرگ Date Format در فایلی بنام Date.xlsx در سلول C27 (تصویر 4-7) ، تاریخ 100 روز کاری بعد از 3/14/2011 ، با در نظر گرفتن روزهای یکشنبه و دوشنبه به‌عنوان روزهای تعطیل با فرمول   WORKDAY.INTL(C23,100,2)محاسبه شده است.در سلول C28 نیز فرمول 100 روز کاری بعد از تاریخ 3/14/2003 ، با در نظر گرفتن تنها روز یکشنبه به‌عنوان روز تعطیل با فرمول WORKDAY.INTL(C23,100,11) محاسبه گردیده است. همچنین می‌توانید مفهوم روز تعطیل را با استفاده از رشته ای از هفت یک و صفر مشخص نمایید، به این شکل که اعداد یک شاخص روز تعطیل و اولین ورودی رشته روز دوشنبه، دومین روز سه شنبه و … مشخص شده باشند. بنابر این در سلول D26و D27 نتایج قبلی به ترتیب با فرمول های WORKDAY.INTL(C23,100,”1000001″) و  WORKDAY.INTL(C23,100,”0000001″) کپی می شود.

چگونه می‌توان تعداد روزهای بین دو تاریخ را مشخص کرد؟

کلید حل این مسئله استفاده از تابع NETWORKDAYS می‌باشد. دستور زبان برای این تابع عبارت است ازNETWORKDAYS(start_date,end_date,[holidays]) در حالی که Holidays آرگومانی انتخابی است که محدوده سلولهایی را که می خواهید به‌عنوان روز تعطیل شمرده شوند را شناسایی می‌کند. تابعNETWORKDAYS تعداد روزهای کاری بین

Start_date‌ و end_dateرا با حذف تعطیلات آخر هفته و هر نوع تعطیلی فهرست شده دیگری به ما می دهد.

برای دیدن تصویری از تابع NETWORKDAYS به سلولC18 در کاربرگ Date Format در فایلی بنامDates.Xlsx که حاوی فرمول NETWORKDAYS(C14,C15)  است نگاه کنید. این فرمول، عدد نشان دهنده روزهای کاری بین تاریخ‌های 1/3/2003 ‌و 8/4/2003 را به ما می دهد که 152 است. فرمول: NETWORKDAYS(C14,C15,F17:F18) درسلول C17 عدد مربوط به روزهای کاری بین 1/3/2003 و 1/3/2003 را به ما می دهد و روزهای تعطیل یادبود مارتین لوترکینگ و استقلال را از آن حذف می‌کند. پاسخ 152-2=150‌است.

تابع NETWORKDAYS.INTL برای اولین‌بار در اکسل 2010 معرفی شد. تابع NETWORKDAYS.INTL  درست مثل تابع WORKDAYS.INTL به شما اجازه می‌دهد تا تعاریف روزهای هفته را به‌دلخواه تغییر دهید. مثلاً در سلول C31 از کاربرگ DATE FORMAT در فایل Dates.xlsx ،تعداد روزهای کاری یعنی (373) روز در میان تاریخ‌های  3/14/2011 و 8/16/2012 با درنظرگرفتن روزهای یکشنبه و دوشنبه به ‌عنوان روزهای تعطیل با فرمول NETWORKDAYS.INTL(C23,C24,2)  محاسبه شده است. (تصویر 4-7 را ببینید) همان نتیجه در سلول D31 با فرمول:  NETWORKDAYS.INTL(C23,C24,”1000001″) به دست آمده است.

تصویر 4-7  مثال‌هایی از توابع تاریخ بین‌المللی

 500 تاریخ مختلف وارد شده در کاربرگ اکسلی وجود دارد. چگونه می‌توان فرمولی نوشت که از هریک از تاریخ‌ها ماه، سال، روز ماه و روز هفته را استخراج نماید؟

کاربرگ DATE FORMAT  در فایل Dates.xlsx (تصویر 3-7) چند تاریخ را در محدوده B5:B10 فهرست کرده است، می بینید که از چهار فرمول مختلف در سلول B5 و سلول‌های محدوده B7:B9 استفاده شده تا تاریخ چهارم ژانویه سال 2003  نمایش داده شود.

در ستون‌های D:G  سال، ماه و روز از ماه و روز هفته در هر تاریخ استخراج شده است. باکپی کردن فرمول YEAR(B5) از سلول D5 در محدوده D6:D10 سال را از هر کدام از تاریخ‌ها استخراج می نماییم . با کپی کردن فرمولMONTH(B5) از سلول E5  به محدوده E6:E10 قسمت ماه هر بخش از تاریخ ( 1برابر با ژانویه، 2 برابر با فوریه و به همین روال) جدا می شود. با کپی کردن فرمول DAYS(B5) از سلول F5 به محدوده (F6:F11) روز ازماه هر تاریخ جدا می گردد. بالاخره با کپی کردن فرمولWEEKDAY(B5,1) از سلول G5  به محدوده G6:G10 روز هفته از هریک از تاریخ‌ها جدامی شود.

وقتی‌که آخرین آرگومان تابع WEEKDAY عدد 1 باشد بنابراین یک برابر با یکشنبه، 2 برابر با دوشنبه و غیره خواهد بود. وقتی‌که آخرین آرگومان عدد 2 باشد بنابراین عدد یک برابر با دوشنبه، دوبرابر با سه‌شنبه و به همین روال ادامه خواهد یافت. وقتی‌که آخرین آرگومان عدد سه باشد بنابراین صفر برابر با دوشنبه، یک برابر با سه‌شنبه و به همین روال ادامه خواهد یافت.

سال، ماه و روز ماه از تاریخی در اختیارم قرار داده شده است. آیا راهی ساده وجود دارد که تاریخ واقعی را بازیابی کرد؟

تابع DATE که آرگومان‌های آن DATE(year,month,day) می‌باشند تاریخ سال، ماه و روز موردنظر از روز ماه را ارائه می‌دهد. در کتاب کار Date Format با کپی‌کردن فرمول DATE(D5,E5,F5) از سلول H5 به سلول‌های H5:H10 تاریخ‌هایی را که کار با آنها شروع شده است را به حالت اولیه برمی‌گرداند.

کسب و کار من در زمینه خریدوفروش اتومبیل است. درمواردی اطلاعاتی مربوط به تاریخ خرید وتاریخ فروش ماشین ها را ثبت کرده ام. آیا می‌توانم به‌راحتی تعیین کنم که ماشین‌ها را چند ماه نگه‌داشته ام؟

تابعDATEDIF به‌راحتی می تواند تعداد سالها،ماه ها ویا روزهای کامل بین دو تاریخ را مشخص نماید. در فایلی بنام Datedif.xlsx( تصویر 5-7) می‌توانید ببینید که اتومبیلی در تاریخ 10/15/2016  خریداری و در تاریخ4/10/2018  فروخته شده ،این ماشین چند سال کامل، ماه یا روز در آنجا نگهداشته شده است؟ دستور زبان تابع DATEDIF عبارت است از DATEDIF(statedate,enddate,time unit) . اگر به‌جای عبارت unit ( تعداد واحد) از حرف “y” استفاده کنیم آن وقت تعداد سالهای تکمیل شده میان تاریخ ابتدا و تاریخ انتها را بدست خواهید آورد؛ اگر به‌جای آن حرف  m”” را بنویسیم شما تعداد ماه های کامل شده بین تاریخ ابتدا و انتها را بدست خواهید آورد و اگر به‌جای آن حرف” d” را بنویسیم تعداد روزهای تکمیل شده میان تاریخ ابتدا و تاریخ انتها را بدست خواهید آورد. بنابر این وارد کردن فرمولDATEDIF(D4,D5,”y”) در سلول D6 نشان می دهد که اتومبیل برای یک سال کامل در آنجا نگهداری شده بوده. وارد کردن فرمول DATEDIF(D4,D5,”m”) در سلول D7 نشان می دهد که اتومبیل به مدت 17 ماه در آنجا نگه داشته شده بوده. وارد کردن فرمول DATEDIF(D4,D5,”d”) در سلول D7 نشان می دهد که اتومبیل به مدت 543 روز در آنجا نگه داری شده بوده. بهتر است بدانید که تابعDATEDIF در لیست توابع  function wizard درج نشده است.

تصویر 5-7 استفاده از تابع DATEDIF

 چگونه می‌توان تاریخی ثابت (بدون تغییر) در یک کتاب کار اکسل قرار داد؟

واردکردن فرمول =TODAY() در سلول همواره تاریخ جاری همان روز را برمی‌گرداند. اگر بخواهیم کاربرگی ایجاد کنیم، شاید دوست داشته باشیم که تاریخ ایجاد آن همواره در کاربرگ نمایش داده شود. فشار کلیدهای Ctrl+; (کنترل به‌اضافه سمی‌کالن) همواره تاریخ جاری را در سلول قرار می‌دهد و برخلاف تاریخ نمایش‌داده‌شده با فرمول =TODAY() ، این تاریخ هرگز تغییر نمی‌کند. کاربرگ نشان‌داده ‌شده در تصویر 6-7 (فایل مربوطه بنام Staticdate.xlsx را نگاه کنید) در تاریخ یازدهم ژوئن سال 2018 ایجاد شده است و این تاریخ همواره در سلولF6 نمایش داده می‌شود.

تصویر 6-7 استفاده از کلیدهای ترکیبیCtrl+; برای ایجاد یک تاریخ ثابت

مسائل این فصل:

• عدد سریال برای تاریخ 25 ژانویه سال 2006 چیست؟
• عدد سریال برای تاریخ 14 فوریه سال 1950 چیست؟
• عدد سریال 4526 چه تاریخ واقعی را نشان می‌دهد؟
• عدد سریال 45000 چه تاریخ واقعی را نشان می‌دهد؟
• تاریخ روزی را مشخص کنید که 74 روز کاری بعد از تاریخ امروز باشد (روزهای تعطیل راهم شامل شود)
• تاریخ روزی را مشخص کنید که 74 روز کاری بعد از تاریخ امروز باشد (روزهای تعطیل را شامل شود اما روزهای کریسمس، روز سال نو و روز استقلال را از آن جدا کند)
• چند روز کاری (شامل روزهای تعطیل) میان تاریخ‌های 1 ژوئیه 2005 و 15 آگوست 2006 قرار دارد؟
• چند روز کاری (شامل روزهای تعطیل به‌جز روز کریسمس، روز اول سال نو و روز استقلال) میان تاریخ‌های 1 ژوئیه 2005 و 15 آگوست 2066 وجود دارد؟

فایلی بنامDatep.xlsx حاوی صدها تاریخ است. از این فایل برای پاسخ‌دادن به مجموعه سؤالات بعدی استفاده کنید.

• ماه، سال، روز ماه و روز هفته هریک از تاریخ‌ها را پیدا کنید.
• هریک از تاریخ‌ها را به شکل اعداد سریال نمایش دهید.
• پروژه‌ای در 4 دسامبر سال 2005 آغاز می‌شود. پروژه شامل سه فعالیت است: فعالیت اول، دوم و سوم. فعالیت دوم می‌تواند روز بعد از پایان فعالیت اول آغاز شود. فعالیت سوم می‌تواند روز بعد از اتمام فعالیت دوم شروع شود. کاربرگی ایجاد نمایید که بتوانید دوره انجام سه فعالیت را در آن وارد کرده تا بتواند هم ماه و هم روزی را که در طی آن هر فعالیت تکمیل شده است را محاسبه و نمایش دهد.
• سهامی در 29 ژوئیه سال 2005 خریداری و در 30 دسامبر سال 2005 فروخته شده است. در روزهای تعطیل کارگر، کریسمس و شکرگزاری تبادل بازار بورس سهام بسته است. فهرستی از تاریخ‌هایی که بازار بورس در طول زمانی که ما سهام را در اختیار داشته‌ایم باز بوده است را ایجاد نمایید.
• فایلی بنامxlsx حاوی تاریخ‌هایی است که چندین اتومبیل خریداری و فروخته شده‌اند. مشخص کنید که هر اتومبیل چند ماه و چند سال نگه‌داری شده است.
• تاریخی را در نظر گرفته و راهی پیدا کنید تا اکسل را وادار نمایید آخرین روز از ماه آن تاریخ را محاسبه نماید. توجه کنید که: DATE(2005,13,1) به طرز شگفت انگیزی تاریخ 1/1/2006 را برمی‌گرداند.
• تاریخی به شما داده شده است، چگونه می‌توانید محاسبه کنید که چه روزی از سال است؟ مثلاً تاریخ4/15/2020  چه روزی از سال است؟ فرمولDATES(2020,1,0) عدد سریال برای روز صفر ماه ژانویه سال 2020 را به ما می دهد و اکسل این تاریخ را به‌عنوان 31 دسامبر سال 2019 تلقی خواهد گرفت.
• در روستای فریدونیا، روزهای سه‌شنبه و چهارشنبه برای کارمندان روز تعطیل حساب می‌شود. تاریخ 200 روزکاری بعد از 5/3/2013 چیست؟
• در لور آمپره، روزهای جمعه و شنبه روز تعطیل محسوب می‌شوند. همچنین روز ولنتاین نیز تعطیل حساب می‌شود. محاسبه کنید چند روز کاری بین تاریخ‌های1/10/2014 و5/31/2015  وجود دارد.
• فرض کنید اولین فصل سال (یک چهارم اول سال) ژانویه تا مارس، دومین فصل آوریل تا ژوئن است و به همین ترتیب. فرمولی بنویسید تا تاریخ اولین روز از هر فصل را نشان دهد.
• از تعریف فصل‌های تمرین قبلی استفاده کنید، فرمولی بنویسید که با هر تاریخ داده شده، آخرین روز فصل قبل را محاسبه نماید.
• کاربرگی ایجاد کنید که در آن هر فردی بتواند تاریخ تولد خود را وارده کرده و صفحه سن واقعی او را نشان دهد.
• روز یادبود همواره آخرین دوشنبه روز ماه مه محسوب می‌شود. کاربرگی ایجاد نمایید که بتوانید سال را در آن وارد کنید و کاربرگ تاریخ روز یادبود را نشان دهد.
• کاربرگی ایجاد نمایید که همیشه 50 روز کاری آینده را فهرست کند (فرض کنید که هیچ روز تعطیلی وجود ندارد و دوشنبه‌ تا جمعه روز کاری حساب شوند)
• گرگ وینستون برای مسئولین فدرال کار می‌کند و در سال 2016 سه تعطیلی فدرال را مرخصی گرفت. (در اینترنت این تعطیلات فدرال را جستجو کنید) گرگ همچنین از تاریخ 1/8/2016 همه جمعه‌ها را به همراه تمام تعطیلات آخر هفته را مرخصی گرفت. فهرستی از تمام روزهایی که گرگ در سال 2016 کارکرده است را تهیه کنید.