تاریخ و زمان در Excel یکی از آن مواردی است که اگر مفهوم آنرا ندانیم قطعا با مشکلات زیادی در Excel روبرو خواهیم شد ، محاسبات ما غلط از آب در می آیند . محاسبه زمان ورود و خروج کارکنان شرکت ، محاسبات زمان کارکردن یک ماشین در یک واحد صنعتی و محاسبه اضافه کاری که در یک ماه انجام داده اید کاملا وابسته به مفهوم زمان و تاریخ در Excel هستند.
در ایم مقاله به صورت کامل مفاهیم تاریخ و زمان در اکسل توضیح داده شده است و با مثالهای متنوعی کاربرد توابع زمان و تاریخ Excel ذکر گردیده است.
یکی از مهمترین مسائلی که کارشناسان حسابداری در Excel با آن هر روزه سرو کار دارند محاسبه زمان ورود و خروج پرسنل و زمان کارکرد روزانه آنها است که اگر از فرمول IF استفاده کنید به سادگی نخواهید توانست آنرا پیاده سازی نمایید اما با مطالعه فرمول‌های شرطی زمان برای محاسبه زمان کارکرد به سادگی قادر به انجام اینکار خواهید بود.
فهرست موضوعات این مقاله عبارتند از :
- منطق تاریخ و زمان در اکسل
- کلیدهای میانبر format cells
- مفهوم ساعت در اکسل
- مثال: محاسبه دستمزد بر اساس ساعت کارکرد
- مثال: جمع ساعات کارکرد در یک ماه
- نکاتی در مورد ساعت و زمان اکسل
- نحوه تایپ صحیح تاریخ میلادی در اکسل
- تنظیمات control panel
آموزش مفهوم تاریخ و زمان در اکسل
آموزش تاریخ و زمان در اکسل این مطلب را اینگونه شروع می‌کنیم، ابتدا در سلولی از اکسل یک تاریخ میلادی را تایپ کنید. مثلا بنویسید 8/8 خواهید دید که اکسل این عدد را به تاریخ تفسیر می‌کند و بعد از زدن Enter، در سلول 2024/08/08 را خواهید دید.( وقتی که سال تاریخی را نمی‌زنیم، اکسل تاریخ را از روی ویندوز می‌خواند)
حال در همان سلول یک عددی تایپ کنید مثلا 1000، خواهید دید که اکسل به جای عدد، یک تاریخ نوشته میشود. خوب حالا می‌توانید همین کار را برای یک ساعت/ زمان نیز انجام دهید مثلا در یک سلول ساعت 1:20 را وارد نمایید و بعد بروید و در همین سلول عددی تایپ کنید مثلا 1.2 یا 0.5 و خواهید دید که این اعداد به زمان تبدیل می‌شوند.
پرسش) آیا می توانید پیش بینی کنید که اگر در سلولی عدد 100.5 تایپ شود نتیجه زمان یا تاریخ آن چند می شود. این تغییرات از چه الگویی پیروی می‌کنند.
در این مقاله پاسخ این پرسش را خواهیم داد.
یادآوری Format Cell اکسل
وقتی که در یک سلول مقداری تایپ میشود، اکسل این مقدار را بررسی می‌کند و بنابر آن مقدار، format این سلول را تعیین می‌کند.
مثلا اگر در یک سلول شما مقادیر زمان و تاریخ را تایپ کنید اکسل فرمت آن سلول را به یکی از انواع Date یا Time تغییر می‌دهد.
تذکر) تمامی سلول‌های اکسل در حالت پیش فرض Format Cells آنها روی گزینه General تنظیم شده است. معنی General یعنی اینکه خود اکسل تشخیص می‌دهد که Format Cells آن خانه چگونه باشد. از این به بعد هر مقداری که در این سلول تایپ شود نیز به همین فرمت تبدیل خواهد شد بنابراین اگر در سلولی یک تاریخ مثلا 8/5 درج شود، از این پس هر عددی که در این سلول تایپ شود توسط اکسل به تاریخ متناظرش تبدیل خواهد شد.
منطق تاریخ و زمان در اکسل
برای ما انسان‌ها تاریخ و زمان یک مفهوم (و احتمالا نوعی تجربه) است اما برای کامپیوتر فقط عدد قابل فهم می‌باشد، بنابراین در اکسل (و به صورت کلی در دنیای کامپیوترها) هر تاریخ یا زمان را باید به یک عدد تبدیل کرد و برای اینکه این کار انجام شود بایستی یک مبدا زمان (لحظه‌ای که زمان برابر صفر است) داشته باشیم و سپس تمامی زمان‌ها را با این صفر مقایسه کنیم،
مثلاً بگوییم که تاریخ X ما انسان‌های معادل است با عدد Y.
نکته: در واقع ما از یک نگاشت استفاده می‌کنیم و هر عدد روی محور را معادل یک تاریخ فرض می‌کنیم.
مبدا زمان ( یعنی لحظه 0) در اکسل برابر ساعت ۱۲ شب در روز ۱ ژانویه ۱۹۰۰ میلادی
دقت داشته باشید که در این لحظه 0 ماه ژانویه دارد شروع میشود و همچنین روز ۱ ماه ژانویه هم شروع میشود و بنابراین:
عدد 0 متناظر 0 ژانویه سال 1900 ساعت 12 شب است.
عدد 1 متناظر 1 ژانویه سال 1900 ساعت 12 شب است.
عدد 28710 متناظر 8 آگوست سال 1978 ساعت 12 شب است.
برای درک بهتر کافی است که تصویر زیر را با دقت بررسی نمایید:
پس دانستیم اگر در یک سلول که در آن تاریخی نوشته شده است هر عددصحیحی تایپ کنیم، این عدد تعداد روزی می شود که از لحظه مبدا زمان ما یعنی صفر گذشته است و تاریخ آن روز را به ما خواهد داد.
تمرین) بگویید که امروز از لحظه صفر اکسل چند روز سپری شده است؟
حل) ابتدا در یک سلول از اکسل کلید ; + Ctrl (کنترل) را بزنید (اگر نمی‌دانید که ; در کجای کیبورد است، به «ک» نگاه کنید) تا تاریخ امروز در سلول وارد شود.
سپس کافی است که Format این سلول را از حالت تاریخ خارج کنید (مثلا General یا Number کنید) و خواهید دید که در آن سلول یک عدد صحیح نمایش داده میشود که نشان دهنده تعداد روزهای گذشته از 0 ژانویه 1900 میلادی است.
میانبرهای مربوط به Format Cells در اکسل
برای تغییر سریع Format سلول‌های می‌توانید از کلیدهای ترکیبی زیر هم استفاده کنید:
| کلیدهای میانبر | توضیحات |
|---|---|
| Ctrl+Shift+~ | اعمال فرمت عمومی (General) |
| Ctrl+Shift+$ | اعمال فرمت ارز با دو رقم اعشار (اعداد منفی داخل پرانتز) |
| Ctrl+Shift+% | اعمال فرمت درصد بدون رقم اعشار |
| Ctrl+Shift+^ | اعمال فرمت نمایی با دو رقم اعشار |
| Ctrl+Shift+# | اعمال فرمت تاریخ (روز، ماه، سال) |
| Ctrl+Shift+@ | اعمال فرمت زمان (ساعت، دقیقه، AM/PM) |
| Ctrl+Shift+! | اعمال فرمت عددی با دو رقم اعشار، جداکننده هزارگان و علامت منفی برای اعداد منفی |
| Ctrl+Shift+* | انتخاب ناحیه فعلی اطراف سلول فعال |
| Ctrl+Shift+: | وارد کردن تاریخ جاری |
| Ctrl+; | وارد کردن زمان جاری |
| Ctrl+' | کپی کردن فرمول سلول بالایی به سلول فعال یا نوار فرمول |
تذکر) همیشه و همه جا مبدا تاریخ ۱۹۰۰ میلادی نیست مثلا در کامپیوترهای Macintosh مبدا تاریخ 1904 میلادی است و اگر شما چنین فایلی را داشته باشید باید در Excel Options تنظیم مربوطه را فعال کنید.
مفهوم و محاسبه ساعت در اکسل
تا اینجا منطق تاریخ بیان شد اما برای زمان اطلاعات ما کافی نیست و باید ببینیم که اکسل چطور زمان (ساعت) را نگهداری می‌کند.
اگر به تصویر محور مختصات قبلی نگاه کنیم، متوجه می‌شویم که اکسل برای نگهداری تاریخ‌های روز از اعداد صحیح ۱ و ۲ و .... استفاده می‌کند و بین هر عددی تا عدد بعدیش ۲۴ ساعت داریم.
بنابراین در اکسل ساعت به صورت یک عدد اعشاری خواهد بود و مشخص می‌کند که چه کسری از یک روز سپری شده ست.
مثلا عدد 1.5 را در نظر بگیرید که عدد 1 آن یعنی 1 روز از صفر ژانویه ۱۹۰۰ میلادی گذشته است بنابراین تاریخ ما میشود ۱ ژانویه ۱۹۰۰ میلادی. اما 0.5 آن عدد مشخص می‌کند که نیمی از روز هم گذشته است بنابراین ساعت ما 12 ظهر خواهد بود و عدد 1.5 در اکسل یعنی ۱۲ ظهر ۱ ژانویه سال ۱۹۰۰ میلادی.
بنابراین:
عدد 0 متناظر مبداء تاریخ اکسل یعنی ۱۲ شب در تاریخ ۱ ژانویه ۱۹۰۰ میلادی است.
عدد 0.5 یعنی نصف روز از مبدا گذشته است و میشود ساعت ۱۲ ظهر در تاریخ ۱ ژانویه ۱۹۰۰ میلادی.
عدد 1.5 یعنی 1 روز و نیم از مبداء گذشته است و میشود ساعت ۱۲ ظهر در تاریخ ۱ ژانویه ۱۹۰۰ میلادی.
عدد 1.75 یعنی ۱ روز و دو سوم روز از مبدا گذشته است و میشود ساعت ۶ بعد از ظهر در تاریخ ۱ ژانویه ۱۹۰۰ میلادی.
محاسبه ساده برای تبدیل کسر از روز به ساعت
همانطور که گفتیم هر عدد اعشاری یعنی چه کسری از روز سپری شده است و برای محاسبه ساعت کافی است که آن قسمت اعشار را در عدد 24 ضرب کنیم:
عدد 0.5 یعنی 0.5 * 24 که میشود ۱۲ ظهر
عدد 0.75 یعنی 0.75 * 24 که میشود ساعت ۶ بعد از ظهر
محاسبه بر روی ساعت در اکسل
مثال اول)
فرض کنید که قرار است حقوق کارگرهای روزمزد یک کارگاه محاسبه شود. قرار است به هر کارگر ساعتی ۱۰۰۰ تومان دستمزد داده شود. سلول‌های لازم برای این کار را به ترتیب زیر در نظر می‌گیریم:
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | نام | ساعت ورود | ساعت خروج | کارکرد |
| 2 | آرش | 6:00 | 14:00 | =D2-C2 |
| 3 | علی | 6:30 | 16:00 | 9:30 |
| 4 | رضا | 7:00 | 12:00 | 5:00 |
| 5 | کاوه | 7:30 | 17:25 | 9:55 |
نکته ۱: Excel به سادگی زمان‌ها را از هم کم می‌کند و نتیجه حاصل به طور اتوماتیک از نوع TIME خواهد بود.
حال می‌بایستی دستمزد هر کارگر را حساب کنیم. فرض کنید برای آرش این کار را به فرمول: E2*1000 انجام دهیم. خوب نتیجه را اگر بررسی کنید خواهید دید که یک عدد منطقی نمی‌شود چون آرش ۸ ساعت کار کرده و باید ۸۰۰۰ تومان حقوق بگیرید. اما خواهیم دید که عدد این سلول -البته بعد از آنکه فرمت سلول را General یا Number کردیم - یک عدد عجیبی میشود
حال ما می‌دانیم که منطق این عدد عجیب چیست. در واقع اکسل متناظر ساعت ۸:۰۰ را در نظر گرفته (که یک عدد اعشاری به صورت 0.33333 است) و سپس آنرا در ۱۰۰۰ ضرب کرده که این عدد عجیب حاصل شده است.
برای حل این مسئله فرمول زیر را برای محاسبه دستمزد باید تایپ کنید:
=(D2 - D1) * 24 * 1000در واقع ما با این کار داریم مقیاس ساعت که در اکسل عددی بین 0 تا 1 است را به مقیاس 0 تا 24 تبدیل می‌کنیم تا بتوانیم دستمزد فرد را حساب کنیم.
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | نام | ساعت ورود | ساعت خروج | محاسبه دستمزد |
| 2 | آرش | 6:00 | 14:00 | =(D2 - D1) * 24 * 1000 |
| 3 | علی | 6:30 | 16:00 | 9500 |
| 4 | رضا | 7:00 | 12:00 | 5000 |
| 5 | کاوه | 7:30 | 17:25 | 9916.6667 |
مثال دوم:
داوود آخر هر ماه ساعتهای زیاد مشغول محاسبه تعداد ساعت کارکرد آن ماه بود، یعنی میخواست ببیند که کلا چند ساعت کار کرده است و خوب اینکار را با دست انجام میداد حال میخواهد با Excel اینکار را انجام دهد اما ......
[
| A | B | C | D | E |
|---|---|---|---|---|
| روز هفته | هفته ۱ | هفته ۲ | هفته ۳ | هفته ۴ |
| SATURDAY | 8:00 | 11:00 | 8:00 | 8:00 |
| SUNDAY | 9:00 | 11:00 | 9:00 | 8:00 |
| MONDAY | 10:00 | 11:00 | 10:00 | 8:00 |
| TUESDAY | 8:00 | 8:00 | 10:00 | 8:00 |
| WEDNESDAY | 12:00 | 12:00 | 12:00 | 8:00 |
| THURSDAY | 8:00 | 8:00 | 8:00 | 8:00 |
| =SUM(B2:E7) |
جالب است که انگار Excel نمی تواند این مقادیر را با هم جمع کند چون در آخر می دهد 3:00
لطفا یک دقیقه فکر کنید و حدس بزنید که از دید Excel چه محاسبه ای انجام می شود.
(يا كليد Ctrl+1) / FORMAT → CELLS CUSTOME→ m/d/yyyy hmm
راهنمایی: به عنوان فرمت این Cell زرد رنگ انتخاب کنید که هم تاریخ و هم زمان را نشان دهد.
خوب، می‌بینید که اکسل دارد درست عمل می‌کند. در واقع این زمان‌ها را با هم جمع زده و هر ۲۴ ساعت را یک روز در نظر گرفته. بنابراین داوود ۹ روز و ۳ ساعت کامل کار کرده است.
| A | B | C | D | E |
|---|---|---|---|---|
| روز هفته | هفته 1 | هفته 2 | هفته 3 | هفته 4 |
| SATURDAY | 8:00 | 11:00 | 8:00 | 8:00 |
| SUNDAY | 8:00 | 11:00 | 9:00 | 8:00 |
| MONDAY | 8:00 | 11:00 | 10:00 | 9:00 |
| TUESDAY | 8:00 | 8:00 | 8:00 | 8:00 |
| WEDNESDAY | 8:00 | 12:00 | 12:00 | 12:00 |
| 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) تابع Now خواهند شد.
- تذکر: کلیدهای Ctrl+؛ و Ctrl+Shift+؛ به ترتیب تاریخ و زمان جاری سیستم را برمی‌گرداند و با refresh شدن عوض نمی‌شوند.
- تذکر: تا آنجایی که ممکن است در فرمول‌ها از تابع Now استفاده نکنید، زیرا این تابع از نوع Volatile است یعنی این تابع در لحظه محاسبه میشود و باعث کند شدن اکسل میشود.
وقتی که شما یک زمان (مثلاً ۱۲:۲۰:۳۳) و یا یک تاریخ دارید (مثلاً ۱۸/۵/۲۰۰۷) با یک سری از توابع می‌توانید اطلاعاتی را از داخل این داده‌ها بیرون بکشید که البته چون تاریخ‌ها میلادی هستند برای ما کاربرد زیادی ندارند.
| A | B | C |
|---|---|---|
| 12:25:33 | 8/5/2007 | |
| HOUR(B1) | 12 | |
| MINUTE(B1) | 25 | |
| SECOND(B1) | 33 | |
| YEAR(C1) | 2007.00 | |
| MONTH(C1) | 08 | |
| DAY(C1) | 05 | |
| WEEKDAY(C1) | 1 |
اشتباه تایپ نکنید.
در بسیاری از موارد کاربران تاریخ را اشتباهی تایپ می‌کنند. مثلاً جای ماه و روز را اشتباه می‌زنند و یا به جای اسلش در تاریخ از یک گزینه دیگر مانند (08.12.2009 یا 2009-12-18) استفاده می‌نمایند و اکسل این سلول‌ها را به‌عنوان متن می‌شناسد و در محاسبات مربوط به تاریخ نمی‌شود از این تاریخ‌ها که در واقع متن هستند استفاده نمود. بهترین راه این است که ابتدا کلید Ctrl+؛ را بزنید تا متوجه شوید که اکسل چگونه تاریخ را درج می‌کند و اگر لازم بود از Control panel > Regional and Language Options شکل نمایش تاریخ را عوض کنید.
به عنوان اولین نفر، تجربه یا دیدگاه خود را بنویسید!