دو مورد از اصلی ترین انواع داده ای که کاربران اکسل همواره از آن استفاده میکنند داده های مربوط به زمان و تاریخ در اکسل است. ولی کاربران اکسل به خصوص افرادی که به تازگی کار با نرم افزار اکسل را آغاز کرده اند معمولا در زمینه استفاده از داده ها و توابع مربوط به تاریخ و زمان با مشکل مواجه هستند. دلیل این پیچیدگی این است که اکسل برای ذخیره سازی تاریخ و زمان دارای مکانیزمی است که اغلب افراد از آن باخبر نیستند.

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

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

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

نحوه ذخیره سازی تاریخ توسط اکسل

دلیل اصلی سردرگمی هایی که در کار با زمان وتاریخ برای کاربران ایجاد میشود، نحوه ذخیره سازی این اطلاعات توسط اکسل است. شما احتمالا انتظار دارید اکسل روز، ماه و سال یک تاریخ را به یاد بسپارد ولی شیوه کار اکسل به این صورت نیست.

اکسل تاریخ ها را به صورت عددهای سریالی که ازابتدای سال 1900 شروع میشود، ذخیره سازی میکند. به این معنی که January 1, 1900 بعنوان 1 ، January 2, 1900 بعنوان 2 و … ذخیره سازی میشود و به همین ترتیب ادامه پیدا میکند تا اینکه تاریخ  september12, 2018 که تاریخ امروز است به صورت عدد 43355  توسط اکسل ذخیره سازی میشود.

نکته:

تاریخ های قبل از January 1, 1900 بعنوان تاریخ در اکسل شناسایی نمیشوند به این معنی که هیچ عدد سریالی منفی در این زمینه وجود نخواهد داشت.

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

نحوه ذخیره سازی زمان توسط اکسل

اکسل زمان را نیز درست شبیه به سیستم ذخیره سازی شماره های سریالی که در مورد مقادیر تاریخ استفاده میشود، ذخیره میکند. هر روز از نیمه شب شروع میشود( 12 نیمه شب یا ساعت 00:00( . از آنجایی که هر ساعت 1/24 از هر روز است، بعنوان عدد 0.041666 نشان داده میشود.

برای مثال ساعت 9:00 صبح در روز September 10, 2013 بعنوان 41527.375 ذخیره سازی میشود.

زمانی که یک زمان بدون مشخص کردن تاریخ مطرح میشود، اکسل آن را بعنوان زمان مربوط به روز 1 ژانویه 1900 ذخیره میکند برای مثال ساعت 3:00 بعدالظهر 0.625 ذخیره میشود.

دقیقه و ثانیه نیز به همین شکل و با استفاده از همین مکانیزم در اکسل عمل میکنند.

یک دقیقه 1/60 یک ساعت، که هر ساعت 1/24 هر روز است و در نتیجه هر دقیقه 1/1440 هر روز است که مقدار عددی آن مقدار 0.00069444… میشود.

هر ثانیه 1/60 از هر دقیقه است و هر دقیقه 1/60 هر ساعت و هر ساعت 1/24 هر روز است که در نتیجه هر ثانیه 1/86400 هر روز است و مقدار عددی آن برابر با 0.00001157407… میشود.

کار با زمان و تاریخ در اکسل

توابع ()DATE و ()TIME

البته شما الزامی به دانستن مقادیر ذکر شده در بالا ندارید زیرا اکسل برای راحتی کار کاربران توابعی را قرار داده است که به راحتی این مقادیر را بدست میدهند و نیاز شما را در این زمینه رفع میکنند. دو مورد از این توابع ()date و ()time است که ترکیب آنها در زیر ارائه شده است:

=DATE(year, month, day)

 

=TIME(hours, minutes, seconds)

 

این توابع دارای سه پارامتر هستند که سال، ماه و روز ( در تابع Date) و ساعت، دقیقه و ثانیه ( در تابع time) را میگیرند و تاریخ مورد نظر را برمیگردانند. برای مثال برای وارد کردن تاریخ September 10, 2013 می بایست از فرمول زیر استفاده کنید.

=DATE(2013,9,10)

 

که این مقدار به صورت 41527 ذخیره سازی میشود. یا برای وارد کردن ساعت 6:00pm( 18 ساعت) از فرمول زیر استفاده کنید:

=TIME(18,0,0)

این زمان بعنوان 0.75 ذخیره میشود و نشان دهنده ساعت 6 بعدالظهر روز اول ژانویه 1900 است. حال برای نشان دهنده زمان از یک تاریخ خاص میتوانید از ترکیب این دو تابع استفاده کنید. برای مثال ساعت 6 بعدالظهر روز 10 سپتامبر 2013 را میتوان به صورت زیر اضافه کرد:

=DATE(2013,9,10)+TIME(18,0,0)

این مقدار به صورت 41527.75 ذخیره سازی میشود که به این معنی است که اکسل دقیقا تاریخ و ساعت مورد نظر ما را ذخیره سازی میکند.

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

اکسل توابع دیگری نیز پیش بینی کرده است که این توابع کار شما را در کار با تاریخ و زمان در اکسل بسیار تسهیل میکند.

تابع ()Today

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

=TODAY()

تابع ()NOW

این تابع نیز که بسیار شبیه به تابع Today در اکسل است همیشه تاریخ و زمان فعلی را برمیگرداند. تابع NOW در اکسل نیز بسیار شبیه به تابع Today در اکسل است و دارای هیچ پارامتری نیست.

توابع ()EDATE و ()EOMONTH در اکسل

تابع EDATE یکی دیگر از توابع تاریخ در اکسل است که این تابع تاریخ چند ماه آینده را برمیگرداند به این صورت که در پارامتر اول تاریخ شروع و در پارامتر دوم تعداد ماه را مشخص میکنید و اکسل تاریخ مربوط به آینده را برای شما برمیگرداند. . تابع EOMONTH نیز تاریخ آخرین روز ماه را برمیگرداند. ترکیب هرکدام از این دو توابع به شکل زیر است:

=EDATE(start_date, months)

=EOMONTH(start_date, months)

بدست آوردن تاریخ در اکسل

توابع DAY ، MONTH و YEAR

تا به اینجا متوجه شدیم که مکانیزم ذخیره سازی و شناسایی تاریخ و زمان در اکسل به چه شکل است و توابعی وجود دارند که در یافتن تاریخ و زمان مورد نظر به ما کمک میکنند اما با فرض اینکه شما مقدار سریالی یک تاریخ رابدانید نیز قادر به شناسایی آن تاریخ نخواهید بود برای مثال شما به سادگی نمیتوانید متوجه شوید که عدد 2890  نشان دهنده چه تاریخ است. برای حل این مسئله می بایست با تعدادی از توابع دیگر از قبیل توابع DAY() ، MONTH() و YEAR() در اکسل آشنا شوید. ترکیب این توابع به شکل زیر است:

=DAY(serial_number)

=MONTH(serial_number)

=YEAR(serial_number)

 

شما میتوانید یک مقدار سریالی یا آدرس هر سلولی که دارای یک مقدار با فرمت DATE باشد را در درون این فرمول ها قرار دهید. برای مثال اگر مقدار September 10, 2013 در سلول A1 قرار داده شده باشد، این توابع به صورت زیر عمل خواهند کرد:

=DAY(A1) مقدار 10 را برمیگرداند

=MONTH(A1) مقدار 9 را برمیگرداند

.

=YEAR(A1) مقدار 2013 را برمیگرداند

یا شما میتوانید به صورت مستقیم مقدار سریالی را در درون فرمول قرار دهید:

=DAY(41527) که مقدار 10 برگردانده میشود

بدست آوردن زمان در اکسل

توابع SECOND ، HOUR  و MINUTE در اکسل

برای زمان نیز این فرایند دقیقا شبیه به تاریخ است و بنابراین فقط ترکیب توابع را که به ترتیب ساعت، دقیقه و ثانیه یک تاریخ یا یک عدد سریالی را برمیگردانند را نشان میدهند،  را در زیر ارائه میکنیم.

=HOUR(serial_number)

=MINUTE(serial_number)

=SECOND(serial_number)

تبدیل زمان و تاریخ در اکسل به متن

تابع ()TEXT در اکسل

در بعضی از مواقع نیاز است که مقدار تاریخ و ساعت را تبدیل به متن کنیم. برای مثال بسیار اتفاق می افتد که می بایست یک تاریخ را در مجموعه ای از رشته های متنی جستجو کنیم و برای این کار می بایست از ابزارهایی  مثل SERACH و FIND استفاده کنیم. تابع TEXT یک  تابع اصلی در اکسل است که برای تبدیل مقادیر تاریخ و زمان به متن مورد استفاده قرارمیگیرد. ترکیب تابع TEXT به شکل زیر است:

=TEXT(value, format_text)

 

پارامتر VALUE میتواند آدرس سلولی باشد که دارای مقداری با فرمت تاریخ یا زمان است.

پارامتر format_text دارای انواع زیادی از حالت های مختلف است که در جدول زیر نشان داده شده است. شما با قرار دادن یکی از انواع FORMAT TEXT های نشان داده شده در جدول زیر به خروجی متناسب با آن دست پیدا خواهید کرد:

Format_text	توضیحات	خروجی
m	عدد مربوط به ماه را نشان میدهد بدون استفاده از صفر ابتدایی	9
Mm	عدد مربوط به ماه را نشان میدهد همراه با استفاده از صفر ابتدایی	09
Mmm	مخفف سه حرفی ماه	SEP
Mmmm	نام کامل ماه	SEPTEMBER
D	عدد مربوط به روز را نشان میدهد بدون استفاده از صفر ابتدایی	10
Dd	عدد مربوط به روز را نشان میدهد همراه با استفاده از صفر ابتدایی	10
Ddd	مخفف سه حرفی روز	TUE
Dddd	نام کامل روز	TUESDAY
Yy	مخفف دو حرفی سال	13
Yyyy	نام کامل سال	2013
[h]	مقدار تجمعی ساعت	996653
H	مقدار ساعت بدون صفر ابتدایی	5
Hh	مقدار ساعت همراه با صفر ابتدایی	05
[m]	مقدار تجمعی دقیقه	59799187
M	مقدار دقیقه بدون صفر ابتدایی	7
Mm	مقدار دقیقه همراه با صفر ابتدایی	07
[s]	مقدار تجمعی ثانیه	3587951223
S	مقدار ثانیه بدون صفر ابتدایی	3
Ss	مقدار ثانیه همراه با صفر ابتدایی	03

 

نتیجه فرمول را میتوان با کاراکترهای فرمت دهی ساده ای با هم ترکیب کرد تا تبدیل به فرمت های استاندارد تاریخ شود. برای مثال با استفاده از تاریخ September 10, 2013 ساعت 5:07:03am میتوان حالت های استاندارد زیر را ایجاد کرد.

برای آشنایی بیشتر با مفهوم زمان و تاریخ در اکسل و همچنین فرمت های اکسل برای تاریخ و زمان پیشنهاد میکنیم مطلب ” مفهوم تاریخ و زمان در اکسل” را مطالعه فرمایید