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

آموزش این مطلب را من در کلاسها به این صورت شروع می کنم ، در خانه ای از اکسل یک تاریخ (مثلا 8/5) بنویسد  و سپس در همان خانه یک عدد تایپ کنید ، خواهید دید که به جای عدد تاریخ نوشته می شود . خوب حالا می توانید همین کار را برای یک زمان نیز انجام دهید (مثلا 1:20) و بعد بروید و در همین خانه عدد تایپ کنید مثلا 5 یا 10 و یا عدد اعشاری 1.5 خواهید دید که این اعداد به زمان تبدیل می شوند.

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

یادآوری  Format Cell

وقتی که در یک خانه ای مقداری تایپ می شود ، اکسل این مقدار را بررسی می کند و بنابر این مقدار format این خانه را تعیین می کند مثلا اگر درخانه ای شما مقادیر زمان و تاریخ را تایپ کنید اکسل فرمت آن خانه را به یکی از انواع Date/Time تغییر می دهد ( می دانیم که خانه ها در حالت عادی فرمت General دارند ) و از این به بعد هر مقداری که در این خانه تایپ شود نیز به همین فرمت تبدیل خواهد شد بنابراین اگر در خانه ای یک تاریخ مثلا 8/5 درج شود (format à cell  را نگاه کنید) از این به بعد هر عددی که در این خانه تایپ شود توسط اکسل به تاریخ متناظرش تبدیل خواهد شد.

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

برای ما انسانها تاریخ و زمان یک مفهوم است و برای کامپیوتر فقط عدد قابل فهم ، بنابراین در اکسل شما هر تاریخ یا زمان را باید به یک عدد تبدیل کنید و برای اینکه این مفهوم داشته باشد، بایستی یک مبدا زمان (لحظه ای که زمان برابر صفر است داشته باشیم) و سپس تمامی زمانها را با این صفر مقایسه کنیم ، مثلا بگوییم که X روز از صفر گذشته است.

مبدا زمان (صفر) در اکسل برابر 1/0/1900  12:00:00 AM است .  12 شب اول ماه ژانویه سال 1900

(دقت داشته باشید که در این لحظه تازه ماه ژانویه دارد شروع می شود)

خوب بنابراین عدد 1 اگر به تاریخ ترجمه شود یعنی یک روز از این لحظه گذشته بنابراین می شود:

ساعت 12 نیمه شب ، 1 ژانویه سال 1900 میلادی

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

 مثلا عدد 367 را پیش بینی کنید که خواهدشد :  1/1/1901  12:00:00 AM

تمرین : بگویید که از لحظه صفر اکسل چند روز سپری شده است؟

حل : تاریخ ویندوز را صحیح تنظیم کنید و سپس در یک خانه ای از اکسل کلید Ctrl+;   را بزنید (یعنی کنترل را نگه دارید و کلید ;  که همان "ک" است را فشار دهید ) با این کار تاریخ امروز در این خانه درج خواهد شد.

حال کافی است که این تاریخ را به عدد متناظرش تبدیل کنید ، برای اینکار خانه ای که در آن تاریخ امروز درج شده را انتخاب کنید  و به format àcells بروید و گزینه General  را انتخاب کنید.

تذکر: شما شاید 1904 را هم در جاهایی ببینید که این مختص به کامپیوترهای Macintosh  است .

تذکر : جای ماه و سال به صورت پیش فرض اینگونه است که M/d/yyyy   و برای تنظیم آن به مسیر زیر بروید:.

Control panel à regional and language options à customize à …

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

مثلا باز هم یک زمان در یک خانه بنویسید 10:25   حال در همین خانه بروید و عدد تایپ کنید خواهید دید که زمان مرتبا 0:00  نشان داده می شود ، دو حالت وجود دارد یا اکسل نمی فهمد و یا ما نمی فهمیم که اکسل چی فهمیده  ، البته احتمال دیگر هم وجود دارد که قل مراد اصلا نمی فهمد.

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

می دانیم که عدد صحیح تعداد روزهای سپری شده از لحظه صفر اکسل را نشان می دهد واگر به formula bar نگاهی بیاندازید اینرا خودتان خواهید دید.

خلاصه اینکه شما بازهم دارید روز اضافه می کنید و اکسل آن عدد را روز در نظر می گیرد  و نه ساعت.

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

اما معیار و منطق تبدیل قسمت اعشار به ساعت چیست ؟

لحظه صفر یعنی 12 شب اول  ژانویه 1900 و لحظه 1 یعنی 12 شب یکم  ژانویه 1900 .

در واقع اکسل ساعت 12 شب را لحظه 0 و ساعت 12 شب روز بعد را لحظه 1 در نظر می گیرد و 24 ساعت را اینگونه محاسبه می کند با توجه به جدول زیر می توانید این مطلب را بهتر درک کنید:

12 شب	0
6 صبح	0.25
12 ظهر	0.5
6 بعد از ظهر	0.75
12 شب فردا	1

حال می توانید با یک تناسب ساده معین کنید که اگر یک عدد  اعشاری (مثلا 0.3125) را وارد کنید و سپس فرمت خانه به به time تغییر دهید چه اتفاقی خواهد افتاد.

=0.3125*24=7.5 à 7:30

توضیح اینکه شما با ضرب عدد در 24 در واقع عددی که بین 0 تا 1 تعریف شده بوده را بردید و در 0 تا 24 تعریف کرده اید.

استفاده از این منطق در حل مسائل :

مثال اول:

فرض کنید که قرار است  حقوق کارگرهای روز مزد یک کارخانه محاسبه شود قرار است که به هر کارگر ساعتی 1000 تومان دستمزد داده شود، شیتهای لازم را برای اینکار را به ترتیب زیر در نظر خواهیم گرفت :

	A	B	C	D	E
1	نام	ساعت ورود	ساعت خروج	کارکرد	دستمزد
2	آرش	6:00	14:00	=C2-B2
3	علی	6:30	16:00	9:30
4	رضا	7:00	12:00	5:00
5	کاوه	7:30	17:25	9:55

نکته 1: اکسل زمانها را از هم به سادگی کم می کند و نتیجه حاصل به طور اتوماتیک از نوع TIME خواهد بود.

حال می بایستی دستمزد هر کارگر را حساب کنیم ، فرض کنید برای آرش اینکار را به فرمول : =D2*1000 انجام دهیم ، خوب نتیجه را اگر بررسی کنید خواهید دید که یک عدد منطقی نمی شود چون آرش 8 ساعت کارکرده و باید 8000 تومان حقوق بگیرد ، اما عددی که ما داریم می شود : 333.33    (در اکسل 2007 می شود 8:00   و اگر فرمت خانه را GENERAL کنید همین عدد را خواهید دید) .

منطق آن را می دانیم در واقع اکسل آمده و عدد منناظر 8:00  را در نظر گرفته و سپس آنرا در 1000 ضرب کرده که نتیجه غیر منطقی به نظر می رسد ، به زبان دیگر اکسل عدد 8:00 را به صورت 0.333333  در نظر می گیرد.

برای حل این مساله فرمول زیر را در وارد می کنیم که در واقع عدد 0.333333  را به حوزه 24 ساعت می آوریم و با آن دستمزد را محاسبه می کنیم و خواهید دید  که نتبجه درست خواهد شد:

	A	B	C	D
1	نام	ساعت ورود	ساعت خروج	کارکرد	دستمزد
2	آرش	6:00	14:00	8:00	=D2*24*1000
3	علی	6:30	16:00	9:30	9500
4	رضا	7:00	12:00	5:00	5000
5	کاوه	7:30	17:25	9:55	9916.6667

مثال دوم:

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

	A	B	C	D	E
1	روز هفته	هفته 1	هفته 2	هفته 3	هفته 4
2	SATURDAY	8:00	11:00	8:00	8:00
3	SUNDAY	9:00	11:00	9:00	8:00
4	MONDAY	10:00	11:00	10:00	8:00
5	TUESDAY	8:00	8:00	8:00	8:00
6	WEDNESDAY	12:00	12:00	12:00	8:00
7	THURSDAY	8:00	8:00	8:00	8:00
					=SUM(B2:E7)

جالب است که انگار اکسل نمی تواند این مقادیر را با هم جمع کند، چون در آخر می دهد 3:00

لطفا یک دقیقه فکر کنید و حدس بزنید که از دید اکسل چه محاسبه ای انجام می شود.

راهنمایی : به FORMAT à CELLS à CUSTOME à   m/d/yyyy h:mm  یا یکی از اینها را بعنوان فرمت این خانه زرد رنگ انتخاب کنید (که هم تاریخ و هم زمان را نشان دهد)

خوب ، می بینید که اکسل دارد درست عمل می کند در واقع این زمانها را باهم جمع زده و هر 24 ساعت را یک روز در نظر گرفته بنابراین داوود 9 روز و 3 ساعت کامل کارکرده است .

	A	B	C	D	E
1	روز هفته	هفته 1	هفته 2	هفته 3	هفته 4
2	SATURDAY	8:00	11:00	8:00	8:00
3	SUNDAY	9:00	11:00	9:00	8:00
4	MONDAY	10:00	11:00	10:00	8:00
5	TUESDAY	8:00	8:00	8:00	8:00
6	WEDNESDAY	12:00	12:00	12:00	8:00
7	THURSDAY	8:00	8:00	8:00	8:00
					1/9/1900 3:00

اما اگر بخواهید ساعت را نشان دهد : باید درFORMAT CELLS  شما گزینه [h]:mm  را انتخاب کنید (به علامت براکت دقت کنید)

بنابراین در مثال فوق داوود 219:00:00 ساعت کار کرده است.

چند نکته در خصوص تاریخ و زمان :

شما توسط توابع =now() می توانید تاریخ و ساعت جاری را در اکسل به کار ببرید.

تذکر : این تابع در هر زمان مجدد محاسبه می شود و مقدار جاری (یعنی آنچه که الان ساعت کامپیوترتان نشان می دهد) را بر می گرداند .

تذکر :اگر کلید F9 بزنید یا فایل را مجدد باز کنید و یا هر مقداری تایپ کنید و Enter را بزنید ، همه اینها باعث مجدد محاسبه شدن (refresh) این تابع خواهند شد.

تذکر : کلید Ctrl + ; و Ctrl + Shift + ;  به ترتیب تاریخ و زمان جاری سیستم را بر می گردانند و با   refresh شدن عوض نمی شوند.

وقتی که شما یک زمان (مثلا 12:25:33) و یا یک تاریخ دارید (مثلا 8/5/2007) با یک سری از توابع می توانید اطلاعاتی را از داخل این داده ها بیرون بکشید که البته چون تاریخها میلادی هستند برای ما کاربرد زیادی ندارند)

	A	B	C
1	12:25:33	12	=HOUR(B1)
2		25	=MINUTE(B1)
3		33	=SECOND(B1)
4
5	8/5/2007	2007.00	=YEAR(B5)
6		8	=MONTH(B5)
7		5	=DAY(B5)
		1	=WEEKDAY(B5)

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