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

اکسل به مانند دیگر کاربرگ‌های الکترونیکی (E-Worksheets)، قادر به تحلیل داده‌های عددی است. برای محاسبات تاریخ و ساعت در اکسل نیز امکاناتی در نظر گرفته شده است. در حقیقت، اکسل تاریخ و ساعت را به صورت عددی دریافت می‌کند ولی برای نمایش آن‌ها از قالب مناسب بهره می‌گیرد. بنابراین آنچه که ما در سلول‌های کاربرگ هنگام نمایش تاریخ و ساعت مشاهده می‌کنیم با چیزی که واقعا در سلول ذخیره شده، تفاوت دارد. در این نوشتار به موضوع مهم و با اهمیت محاسبات تاریخ در اکسل می‌پردازیم تا با دقت بیشتر و صحت، عملیات محاسباتی روی چنین داده‌هایی را انجام دهیم. به کارگیری توابع مرتبط با تاریخ نیز از مواردی است که در این نوشتار به آن‌ها اشاره خواهیم کرد. البته در مطلب دیگری به نحوه کار با مقادیر ساعت و توابع مرتبط با آن خواهیم پرداخت.

برای آشنایی بیشتر با اصطلاحات پیش‌نیاز این نوشتار، پیشنهاد می‌شود مطالب دیگر مجله فرادرس با عنوان‌های آموزش اکسل (Excel) مقدماتی — به زبان ساده و اکسل و راه‌های انتخاب مجموعه‌ای از سلول‌ها در آن را مطالعه کنید. همچنین خواندن نوشتارهای رنگی کردن یکی در میان سطرهای اکسل — به زبان ساده و فرمول‌ های قالب بندی شرطی (Conditional Formatting) در اکسل — به زبان ساده نیز خالی از لطف نیست.

محاسبات تاریخ در اکسل

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

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

کلیک کنید

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

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

تاریخ و نحوه نمایش آن

فرض کنید، سال تولد شما، اولین روز اولین ماه سال ۱۹۰۰ باشد. این تاریخ را به عنوان روز اول (روز ۱) در نظر می‌گیریم. به تعداد روز‌هایی که از تاریخ تولدتان فاصله گرفته‌اید، تاریخ نسبت می‌دهیم. به این ترتیب منظور از روز ۲۵ ام از ماه اول سال ۱۹۰۰ به معنی ۲۵امین روز تولد شما است. بنابراین عدد ۲۵ برابر با تاریخ ۲۵ ژانویه سال ۱۹۰۰ است.

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

کلیک کنید

به همین ترتیب اگر ۴۵ روز از تولد شما دور شویم، براساس تقویم میلادی تاریخ تولد شما به شکل ۱۵ امین روز از ماه دوم سال ۱۹۰۰ خواهد بود. بنابراین عدد ۴۵ برابر با تاریخ ۱۵ فوریه سال ۱۹۰۰ خواهد بود.

همچنین، اگر امروز برابر با تاریخ ۱۴امین روز از ماه مارس به تقویم میلادی باشد، تعداد روز‌های سپری شده از تاریخ ۱ - ۱ - ۱۹۰۰ برابر است با 43904 روز. بنابراین می‌توان از عدد ۴۳۹۰۴ برای ثبت این تاریخ در اکسل استفاده کرد. بنابراین مشخص است که هر عدد صحیح مثبت را می‌توان در اکسل به عنوان تاریخ در نظر گرفت.

همانطور که می‌دانید، برای تعیین نحوه نمایش اعداد در اکسل، از قالب‌بندی سلول یا دستور Format Cell استفاده می‌کنیم.برای دسترسی به این دستور، کافی است پس از انتخاب سلول یا ناحیه‌ای از سلول‌ها که حاوی مقادیر عددی (یا تاریخی) هستند، از فهرست کلیک راست (Popup Menu)، گزینه ...Format Cells را انتخاب کنید.

پنجره‌ای به مانند تصویر ۱ ظاهر خواهد شد. با انتخاب گزینه Date از فهرست Numbers، فهرستی از قالب‌های مختلف برای نمایش تاریخ، ظاهر می‌شود.

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

جدول ۱: معرفی چند قالب برای نمایش تاریخ

نکته: برای آنکه ترتیب نمایش سال و روز یا ماه را تغییر دهید، می‌توانید با تغییر در گزینه (Locale (Location منطقه یا نقطه جغرافیایی مربوطه را مشخص کنید تا شیوه و قالب آن‌ جا برای نمایش تاریخ به کار رود. توجه داشته باشید که قالب بندی فقط شکل ظاهری محاسبات تاریخ در اکسل را تغییر می‌دهد و ماهیت مقادیر تغییری نمی‌کند. گزینه‌هایی که در لیست قالب‌بندی ساعت با علامت * مشخص شده‌اند، از تنظیمات کنترل پنل تبعیت می‌کنند.

همانطور که مشخص شد، با تغییر قالب اعداد به قالب تاریخ، هر عدد مثبتی را می‌توان به شکل تاریخ در آورد یا هر تاریخی را به صورت یک عدد مثبت نمایش داد. در تصویر ۲، شکل تاریخی برای عدد ۴۰۰۰۰ و همچنین در تصویر ۳، شکل عددی برای تاریخ ۲۰-۲-۲۰۲۰ نشان داده شده است.

البته توجه دارید که آنچه مهم است، مقداری است که در نوار فرمول دیده می‌شود. قالب‌بندی فقط نحوه نمایش اعداد را تغییر می‌دهد.

با توجه به ماهیت عددی برای تاریخ در اکسل، در ادامه به محاسبات و توابع مرتبط با تاریخ خواهیم پرداخت.

محاسبات تاریخ در اکسل بر مبنای فرمول‌نویسی

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

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

کلیک کنید

بنابر ماهیت عددی تاریخ‌ها، کافی است، تاریخ آغاز پروژه را از تاریخ اتمام آن کسر کنیم. همیشه به یاد داشته باشید که تعداد روز‌ها باید مثبت باشند بنابراین هنگام نوشتن چنین فرمولی، ابتدا تاریخ اتمام را مشخص کرده و پس از زدن علامت منفی، تاریخ آغاز را در فرمول وارد می‌کنیم.

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

نکته: عددی که برمبنای آن تاریخ نمایش داده می‌شود، به اصطلاح «شماره سریال تاریخ» (Date Serial number) گفته می‌شود. این اصطلاح بخصوص در زمان استفاده از توابع تاریخی، بسیار مهم است.

مشابه همین عمل را برای جمع کردن یک تاریخ با تعداد روز‌ها نیز می‌توان انجام داد.جمع و تفرق از معمول‌ترین محاسبات تاریخ در اکسل محسوب می‌شوند.

برای مثال فرض کنید که پیمانکار به ما گفته که ۴۵ روزه (بدون احتساب روزهای تعطیل)، پروژه سیم‌کشی ساختمان را اجرا می‌کند. می‌خواهیم بدانیم که اگر این کار در تاریخ ۲۰-۵-۲۰۲۰ آغاز شود، در چه تاریخی پایان خواهد یافت. به این منظور، فرمولی به مانند زیر نتیجه را برایمان مشخص می‌کند. فرض کنید تاریخ شروع پروژه در سلول A1 نوشته شده و می‌خواهیم تاریخ پایان آن را در سلول B1 محاسبه کنیم.

$$ \large B1: = A1 + 45 $$

عبارت $$B1 :$$ به این معنی است که باید در سلول B1 فرمول را وارد کنید.

نکته: در نسخه‌های قدیمی اکسل (قبل از ۲۰۱۶) اگر می‌خواهید نتیجه یک محاسبه را به صورت تاریخی نمایش دهید، باید مقدار محاسبه شده، مثبت باشد در غیر این صورت اکسل اعداد منفی را با نمایش علامت ##### در قالب تاریخی نمایش خواهد داد. در نسخه‌های جدید اکسل، نمایش مقادیر منفی حاصل از تفاضل دو تاریخ امکان‌پذیر است.

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

توابع مربوط به محاسبات تاریخ در اکسل

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

جدول ۲: معرفی توابع مرتبط با محاسبات تاریخ در اکسل به همراه کارکرد پارامترهای آن‌ها

به منظور دسترسی به این توابع کافی است از برگه Formulas گزینه Date & Time را فعال کنید تا لیست توابع تاریخ ظاهر شود. البته این کار را با استفاده از دکمه Fx از نوار فرمول نیز می‌توانید انجام دهید. در پنجره ظاهر شده، گزینه Data & Time را از لیست کشویی Select a Category انتخاب کنید تا در قسمت Select a Function توابع مرتبط با تاریخ و ساعت، دیده شوند. به کمک این توابع محاسبات تاریخ در اکسل را انجام می‌دهیم.

مثال ۱

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

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

مثال ۲

فرض کنید می‌خواهید بدانید ۴ ماه بعد از تاریخ ۲/۲/۲۰۲۰، چه تاریخ و چه روزی از هفته خواهد بود. ابتدا به کمک تابع Edate تاریخ را ۴ ماه جلو می‌بریم. واضح است که تاریخ به صورت ۲/۶/۲۰۲۰ در خواهد آمد.

فیلم آموزش جداول محوری در اکسل یا پیوت تیبل Pivot Tables در فرادرس

کلیک کنید

همانطور که در تصویر ۶ مشاهده می‌کنید، تابع EDATE فقط مقدار سریال تاریخ را نشان می‌دهد و برای نمایش صحیح تاریخ باید قالب سلول (Format Cell) را به شکل تاریخی (Date Format) تغییر دهید.

حال، نتیجه را به کمک تابع WEEKDAY تبدیل به شماره هفته می‌کنیم. این عملیات در تصویر ۷، دیده می‌شود. با توجه به اینکه شماره روز هفته برابر با ۳ است، مشخص است که این روز، سه شنبه (Tuesday) خواهد بود.

مثال ۳

مانند مثال شماره ۲ می‌خواهیم تاریخی که چهار ماه از تاریخ ذکر شده در سلول A1 جلوتر است را مشخص کنیم و مثلا تاریخ پرداخت پروژه‌‌ای که چهار ماه به طول می‌انجامد را مشخص کنیم. در این بین، باید تاریخ آخر ماه مربوط به چهار ماه بعد را استخراج کنیم. همانطور که در جدول ۲، اشاره شد، تابع EOMONTH برای انجام این محاسبه مناسب است. در تصویر ۸ نحوه تنظیم پارامترهای این تابع را مشاهده می‌کنید.

همانطور که مشاهده می‌کنید، چهار ماه بعد از تاریخ ذکر شده در سلول A1 ثبت شده که در ماه ششم سال است که تاریخ آخر آن ماه، ۳۰ام جولای (ماه ششم میلادی) است.

مثال ۴

فرض کنید می‌خواهیم بدانیم از تاریخ آغاز پروژه تا به امروز چند روز سپری شده است. (تاریخ امروز سیستم برابر با ۳/۱۴/۲۰۲۰ است). بنابراین کافی است با تابع TODAY تاریخ امروز را استخراج کرده و از تاریخ آغاز پروژه که همان ۲/۲/۲۰۲۰ است کسر کنیم. در تصویر ۹ چنین محاسباتی صورت گرفته است.

خلاصه و جمع‌بندی

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