بهتر است قبل از پرداختن به مقوله زمان در اکسل ، آموزش قبلی یعنی مفهوم تاریخ در اکسل را ببینید ، در آنجا ما گفتیم که منای محاسبات در اکسل تاریخ 1/1/1900 است که معادل عدد 1 قرار گرفته و هر روزی که می گذرد یک روز به این تاریخ ااضفه می شود .
در واقع زمان در اکسل به صورت عددی ذخیره می شود و ماهیت اعشاری دارد به این صورت عدد 1 معادل 24 ساعت کامل است و عدد 0.5 معادل 12 ساعت میشود .
یعنی اگر ما عدد 0.5 را به 1/1/1900 اضافه کنیم در واقع نتیجه ساعت 12 ظهر روز یکم ژانویه 1900 می باشد و اگر مثلا عدد 0.75 را اضافه کنیم ، انگار که 3/4 یک روز 24 ساعته را اضافه کرده ایم که می شود ساعت 18 یا 6 عصر روز 1/1/1900 در ادامه با مثالهای مختلف بهتر این مفهوم را درک خواهید کرد.
فهرست محتوا
نحوه ورود داده بر حسب زمان در اکسل و نمایش آن
زمان از سه قسمت ساعت و دقیقه و ثانیه تشکیل شده که با : از هم جدا می شوند ، یعنی شما اگر در سلولی عدد 10:20 را وارد کنید اکسل به صورت خودکار آن را به عنوان زمان در نظر خواهد گرفت .
حالا بیایید مثالی را بررسی کنیم تا متوجه شویم که اکسل چگونه با زمان به عنوان عدد برخورد می کند و محاسبات را انجام می دهد .
فرض کنید در سلولی مقدار 09:20:15 وارد شده است ، اگر سلول را انخاب کنیم و در تب Home و قسمت Number شکل نمایشی آن را روی عدد قرار دهیم و مقدار اعشار آن را افزایش دهیم عدد 0.3890625 را نمایش خواهد داد .
بنا بر استانداردی که در ابتدا گفتم برای تبدیل معکوس ، یعنی تبدیل این عدد به ساعت کافیست آن را در عدد 24 ضرب کنیم که عدد 9.3375 به دست خواهد آمد که بخش صحیح آن یعنی عدد 9 بیانگر ساعت خواهد بود .
حالا اگر بخش اعشار آن را که عدد 0.3375 است در عدد 60 ( هر ساعت 60 دقیقه است ) ضرب کنیم مقدار دقیقه آن یعنی عدد 20.25 به دست خواهد آمد و تا اینجا 9:20 به دست آمده و تا حدود زیادی با مفهوم زمان در اکسل آشنا شده ایم ، حالا فقط باید 0.25 باقیمانده در عدد 60 ضرب شود که ثانیه هم محاسبه شود که عدد 15 خواهد بود که همان 9:20:15 خواهد شد .
نکته مهم :
با توضیحاتی که داده شد برای وارد کردن زمان به شکل صحیح باید دقت کنسد که در قسمت ساعت عدد منفی وارد نکنید و هر عددی وارد کنید تقسیم به 24 میشود و در قسمت دقیقه که بعد از : اول وارد می کنید عدد منفی یا عدد بزرگتر از 60 وارد نکنید و در ثانیه هم همینطور ، بزرگتر از 60 و کمتر از 0 وارد نکنید . اگر رعایت گنید ، اکسل داده را زمان در نظر خواهد گرفت در غیر این صورت به شکل متنی لجاظ خواهد شد .
مثلا مقدار 36:25:10 صحیج است و معادل 1.51747 در نظر گرفته می شود ولی 36:61:20 درست نیست و متن لحاظ می شود .
حالتهای مختلف نمایش زمان در اکسل
اگر سلول یا محدوده ای در اکسل دارای مقداری از نوع زمان بود که توضیح دادیم می توانید حالت نمایشی آن را تغییر دهید و برای این منظور کافیست ، محدوده را انتخاب کرده و کلیک راست کنید و گزیه Format Cells بروید و یا کلدیهای Ctrl + 1 را بزنید که پنجره فرمت سلز باز شود ومانند تصویر زیر به تب Number و بخش Time بروید و حالتهای مختلف را مشاهده کنید .
هر کدام از حالتهایی که در تصویر بالا نمایش داده شده برای زمان در اکسل انتخاب کنید در اصل داده تغییر ایجاد نمیکند و همان عدد است که در پس زمینه محاسبات روس آن انجام میشود و این سلیقه و نیاز شماست که تعیین میکند که به شکل 12 ساعته یا AM (قبل از ظهر ) و PM ( بعد از ظهر ) مشاهده کنید و یا با فرمت 24 ساعته بدون ق ظ و ب ظ .
بدیهی است که اگر بخواهید یه جای AM , PM حروف ق ب و ب ظ نمایش داده شود ، کافیست در هیمن پنچره کافیست قسمت Location را روی Persian قرار دهید.
به دست آوردن اختلاف زمان در اکسل
خب وقتی داده ها را به شکل عدد ذخیره می کند مشخص است که به راحتی می تواند آنها را با هم جمع کند و یا از هم کم کند و نتیجه را نمایش دهد . ولی برای درک بهتر مثال می زنیم.
بهترین مثال برای محاسبات زمان می تواند محاسبه میزان کارکرد کارمندان است ، فرض کنید کارمندان در سک ساعتی وارد و در ساعتی هم خارج می شود در این صورت اگر ساعت خروج که ساعت بزرگتر است را منهای ساعت ورود کنیم ، کارکرد برحسب ساعت مشخص خواهد شد .
| A | B | C | D |
| نام | ساعت ورود | ساعت خروج | کاردکرد |
| نادر | 8:10 | 16:15 | 8:05 |
| محمد | 7:50 | 17:20 | 9:30 |
| ارسلان | 8:00 | 13:15 | 5:15 |
| رضا | 9:20 | 18:10 | 8:50 |
در مثال بالا کار بسیار ساده است و در ستون D کافیست بنویسیم : C5-B5 و محاسبات انجام می شود .
اما زمانی که ساعت خروج کوچکتر از ساعت ورود باشد ، در واقع کارمند فردای روزی که ورود کرده ، خارج شود محاسبات درست نخواهد بود ، چرا که عدد منفی در زمان معنی ندارد برای حل این مشکل یک دو راه حل وجود دارد که در ادامه خواهید دید.
در واقع کارمند امروز آمده و روز بعد حارج شده پس باید عدد یک به نتیجه اضافه شود .
روش اول :
با ترکیب تابع IF بررسی کنیم اگر ساعت خروج کوچکتر بود ، عدد یک به نتیجه اضافه شود و در غیر اینصورت به صورت طبیعی تفریق انجام شود .
=IF(D5<C5,1+D5-C5,D5-C5)
روش دوم :
از تابع MOD استفاده کنیم و باقیمانده تقسیم نتیجه محاسبه بر عدد 1 که معادل همان 24 است را به دست آوریم این تابع باعث می شود که نتیجه همواره عددی بین 0 تا 1 باشد .
=MOD(D5-C5,1)
جمع ساعت بالای 24 ساعت
چالش دیگری وجود دارد که در مبحث زمان در اکسل برای شما پیش خواهد آمد بحث جمع ساعت کاری است که احتمالا بالای 24 ساعت می شود ، به عنوان مثال تصویر زیر را ببینید
همانطور که در تصویر هم توضیح داده شده کافیست که شما حالت نمایشی سلولی که حاوی ساعت است را از h:mm به [h]:mm تغییر دهید .
تبدیل ساعت به روز و دقیقه و ثانیه
حال فرض کنید که در محاسبات زمان در اکسل به داده ای بالای 24 ساعت رسیدید مثلا عدد 58:35:40 که 58 ساعت و 35 دقیقه و 40 ثانیه است فرض کنید که این داده در A1 قرار دارد .
=DAY(A1)&"روز و "&HOUR(A1) & "ساعت و" & MINUTE(A1)&"دقیقه و "&SECOND(A1)&"ثانیه"
با توجه به فرمول بالا درک می کنید که در مفهوم زمان در اکسل تابع day , minute , second عمل جداسازی روز و ساعت و دقیقه و ثانیه را انجام می دهند .
