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

۲۹۸۹۶ بازدید
آخرین به‌روزرسانی: ۰۲ اسفند ۱۴۰۲
زمان مطالعه: ۱۰ دقیقه
محاسبات تاریخ در اکسل — راهنمای کاربردی

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

997696

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

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

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

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

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

یک مرد نشسته پشت سیستم کامپیوتری (تصویر تزئینی مطلب محاسبات تاریخ در اکسل)

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

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

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

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

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

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

format cells date
تصویر ۱: پنجره قالب‌بندی تاریخ در اکسل

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

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

ردیفقالب نمایشعملکردتوضیحات
۱3/14/2012*نمایش تاریخ برحسب سال، ماه و روزجداکننده و نوع نمایش ابتدا ماه یا سال برحسب تنظیمات سیستم (Control Panel) مشخص می‌شود.
۲*Wednesday ,March, 14,2012نمایش تاریخ برحسب سال، نام ماه و روزجداکننده و نوع نمایش ابتدا ماه یا سال برحسب تنظیمات سیستم (Control Panel) مشخص می‌شود.
۳2012-03-14نمایش تاریخ برجسب چهار رقم برای سال، ۲ رقم برای ماه و دو رقم برای روزترتیب قرارگیری سال، ماه و روز از چپ به راست است.
۴3 /14نمایش فقط ماه و روز تاریخحداقل فضای برای نمایش ماه و روز
۵3/14/12نمایش سال و ماه روز تاریخحداقل فضا برای نمایش سال،ماه و روز
۶03/14/12
نمایش سال و ماه روز تاریخحداقل فضا برای نمایش سال و روز- با احتساب دو رقم برای ماه
۷14-Mar
نمایش ماه متنی و روزمناسب برای مکاتبات اداری بدون قید سال

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

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

format cells date
تصویر ۲: شکل نمایش تاریخی برای عدد ۴۰۰۰۰

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

date to number
تصویر ۳: نمایش عدد تاریخ ۲۰ - ۲- ۲۰۲۰

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

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

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

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

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

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

subtract two dates
تصویر ۴: تفاصل دو تاریخ (اتمام و آغاز پروژه)

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

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

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

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

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

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

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

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

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

ردیفنام تابععملکردپارامترهاتوضیحات
۱DATEتبدیل تاریخ به شماره سریال تاریخیYear, Month, Dayتبدیل تاریخ (روز-ماه-سال) به شماره سریال- عکس تابع DATEVALUE)
۲DATEVALUEتبدیل شماره سریال به تاریخDate_textتبدیل تاریخ مربوط به پارامتر تابع از  شماره سریال (تعداد روزهای سپری شده از تاریخ مبدا اکسل) به شکل روز-ماه-سال (عکس تابع DATE)
۳DAYنمایش روز از یک تاریخSerial_numberپارامتر این تابع شماره سریال یک تاریخ است.
۴DAY360تعداد روزهای بین دو تاریخ برحسب روزهای ۳۶۰ برای سال مشخص می‌کندstart_date, End_date, Methodپارامترهای اول و دوم، تاریخ شروع و خاتمه را مشخص می‌کنند. پارامتر سوم نیز مقداری منطقی است که با مقدار true شیوه تاریخ آمریکایی و با False شیوه محاسبه تاریخ برحسب استاندارد اروپایی انجام می‌شود.
۵EDATEنمایش تاریخ بعد از گذشت تعداد ماه‌ها از تاریخ مبداstart_date, monthsپارامتر اول، تاریخ مبدا و پارامتر دوم تعداد ماه است. مقدار مثبت برای این پارامتر، تاریخ بعدی و با مقدار منفی تاریخ قبل از تاریخ مبدا برحسب ماه مشخص می‌شود.
۶EOMONTHنمایش تاریخ آخرین روز ماه بعد از گذشت تعداد ماه‌ها از تاریخ مبداstart_date, monthsپارامتر اول، تاریخ مبدا و پارامتر دوم تعداد ماه است. مقدار مثبت برای این پارامتر، تاریخ بعدی و با مقدار منفی تاریخ قبل از تاریخ مبدا برحسب ماه مشخص می‌شود.
۷MONTHنمایش ماه از یک تاریخSerial_numberپارامتر این تابع شماره سریال یک تاریخ است.
8NETWORKDAYSتعداد روزهای کاری در یک بازه تاریخیstart_date, Days, Holidaysپارامتر اول، تاریخ شروع و days، تعداد روزهای سپری شده از تاریخ شروع و Holidays نیز آدرس ناحیه‌ای است که تاریخ‌های غیرکاری در آن ذکر شده است.
9TODAYنمایش و ثبت تاریخ جاری سیستمبدون پارامترثبت تاریخ جاری درون سلول با توجه به قالب‌بندی سلول برای نمایش تاریخ
10WEEKDAYنمایش شماره روز هفته مربوط به یک تاریخ (خروجی با مقدار ۱ تا ۷)serial_number, return_typeپارامتر اول، تاریخ شروع و پارامتر دوم نوع نمایش روز هفته

1= مشخص کردن یکشنبه به عنوان روز اول هفته

2= مشخص کردن روز دوشنبه به عنوان روز اول هفته

3= دوشنبه روز صفرم هفته خواهد بود

11WEEKNUMنمایش شماره هفته از تاریخ مبداserial_number, return_typeپارامتر اول، تاریخ شروع و پارامتر دوم نوع محاسبه شروع هفته

1 = شروع هفته از یکشنبه

2= شروع هفته از دوشنبه

12WORKDAYتاریخ اولین روز کاری بعد از یک تاریخ مشخصstart_date, Days, Holidaysپارامتر اول، تاریخ شروع و days، تعداد روزهای سپری شده از تاریخ شروع است. Holidays نیز آدرس ناحیه‌ای است که تاریخ‌های غیرکاری در آن ذکر شده است.

نزدیک‌ترین تاریخ روز کاری بعد از سپری شدن تعداد روزهای days از تاریه start_date با توجه به تقسیم تعطیلی Holidays خروجی این تابع است.

13YEARنمایش سال از یک تاریخSerial_numberپارامتر این تابع شماره سریال یک تاریخ است.
14YEARFRACفاصله دو تاریخ برحسب سالStart_date, End_Date, Basisپارامترهای اول و دم تاریخ شروع و پایان هستند. پارامتر سوم نیز با توجه به نوع استاندار محاسبه یکی از مقادیر۰  تا ۴ را می‌گیرد.

0= 30/360 آمریکایی

1= تعداد روش های  واقعی/تعدا روزهای واقعی سال

2= تعداد روزهای واقعی / ۳۶۰

3= تعداد روزهای واقعی /۳۶۵

4 = ۳۰ /۳۶۰ اروپایی

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

مثال ۱

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

day function
تصویر ۵: تابع DAY در اکسل

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

مثال ۲

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

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

edate function
تصویر ۶: استفاده از تابع EDATE

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

weekday function
تصویر ۷: استفاده از تابع WEEKDAY

مثال ۳

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

EOMONTH Function
تصویر ۸: به کارگیری تابع EOMONTH برای مشخص کردن تاریخ آخر ماه

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

مثال ۴

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

TODAY FUNCTION
تصویر ۹: استفاده از تابع TODAY در اکسل

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

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

بر اساس رای ۳۵ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
مجله فرادرس
۶ دیدگاه برای «محاسبات تاریخ در اکسل — راهنمای کاربردی»

سلام ستون تاریخ نوشته شده حالا می خواهم سال عوض کنم اما نمی خواهم شماره روز تغییر کنه چون شماره روزها به ترتیب نیست

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

با سلام؛

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

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

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

با تشکر از همراهی شما با مجله فرادرس

با تشکر از همراهی شما با مجله فرادرس

عالی بود.

سلام روز شما بخیر
می خواهم یک تاریخ را با عددی جمع یا تفریق کنم تا تاریخ جدید به من بدهد ، از چه فرمولی غیر از توابع فارسی اکسل می توان استفاده کرد ؟

سلام .
میخوام 7 سال و 5 ماه و 9 روز رو تقسیم بر عدد ثابتی مانند 3 کنم. فرمولش چی میشه؟

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *