زمان و توابع زمان

آخرین به‌روزرسانی: 27 دی 1401

در فصل گذشته به بررسی توابع IF ، IFERROR، IFS، CHOOSE و SWITCH پرداختیم, در این فصل به بررسی زمان و توابع زمان می پردازیم.

سؤال‌هایی که در این فصل به آنها پاسخ داده می‌شود:

• چگونه می‌توان زمان را در اکسل وارد کرد؟
• چگونه می‌توان زمان و تاریخ را در یک سلول وارد کرد؟
• اکسل چگونه محاسبات زمان‌دار را انجام می‌دهد؟
• چگونه می‌توان کاری کرد که کاربرگ همواره زمان جاری را نمایش دهد؟
• چگونه می‌توان از تابعTime برای خلق زمان استفاده کرد؟
• چگونه می‌توان با استفاده از تابعTIMEVALUE یک رشته متنی را به زمان تبدیل کرد؟
• چگونه می‌توان ساعت، دقیقه یا ثانیه را از یک‌زمان داده شده استخراج کرد؟
• باتوجه‌به زمان شروع و پایان کار، چگونه می‌توان تعداد ساعت‌های کار انجام شده یک کارمند را مشخص کرد؟
• هنگامی‌که کل ساعت‌های کار کارمندی را جمع می‌زنم، بیشتر از 24 ساعت به دست نمی‌آورم، اشکال کار در کجا است؟
• چطور می‌توان به‌آسانی رشته‌ای با فاصله زمانی منظم ایجاد کرد؟
• چگونه می‌توان یک‌زمان ثابت (بدون تغییر) را در سلولی قرارداد؟

از فصل هفتم بنام تاریخ‌ها و توابع تاریخ به یاد داریم که نرم‌افزار مایکروسافت اکسل 2019 به تاریخ اول ژانویه سال 1900 عدد سریال 1، به دوم ژانویه سال 1900 عدد سریالی 2 و به همین روال اعداد سریالی خاصی را بخشیده است. اکسل به همین روال عددهای سریالی را به زمان(به‌عنوان کسری از روز 24 ساعته) اختصاص می‌دهد. نقطه شروع نیمه‌شب است بنابراین به ساعت 3:00 پس از نیمه‌شب عدد سریالی 125. اختصاص‌داده‌شده است، به ظهر عدد سریالی 5. اختصاص‌داده‌شده، 6:00 بعدازظهر دارای عدد سریالی 75. می‌باشد و به همین روال. اگر تاریخ و زمان را در سلولی باهم ترکیب کنید، عدد سریالی عبارت خواهد بود از عدد روزهای بعد از یک ژانویه سال 1900، به ‌اضافه کسری که بیانگر زمان داده شده است؛ بنابراین واردکردن تاریخ January 1,2007 در یک سلول(وقتی‌که فرمت General داشته باشد) به شما عدد 39083 را می‌دهد، درحالی‌که واردکردن تاریخ January 1,2007, 6:00 AM عدد سریالی 39083.25  را برمی‌گرداند.

پاسخ به سؤالات این فصل:

چگونه می‌توان زمان را در اکسل وارد کرد؟

برای مشخص‌کردن زمان، می‌بایست یک علامت دو نقطه(:) بعد از ساعت و علامت دو نقطه دیگری قبل از ثانیه‌ها قرار دهید. مثلاً در فایلی به نام Time.xlsx(تصویر 13-1 را ببینید) در سلول C2 زمان هشت و نیم صبح را به شکلAM 8:30 وارد کرده‌ایم. می‌توانستیم این زمان را به‌سادگی به شکل8:30 وارد کنیم. در سلولC3  زمان هشت و نیم بعدازظهر را به‌عنوان 8:30PM وارد کرده‌ایم. همان‌طور که در سلول D3 مشاهده می‌شود می‌توانستیم ساعت هشت و نیم بعدازظهر را به شیوه 24 ساعته به شکل20:30 وارد کنیم. در سلول A4 فرمول =TIME(15,10,30) را وارد کرده‌ایم که زمان 3:10:30 PM را به ما می‌دهد. این زمان سی ثانیه پس از ساعت سه و ده دقیقه بعدازظهر را به ما نشان می‌دهد.

تصویر 1-13 مثال‌هایی از فرمت‌های زمان

چگونه می‌توان زمان و تاریخ را در یک سلول وارد کرد؟

برای انجام این کار به‌سادگی یک فضای خالی بعد از تاریخ قرار داده و بعد زمان را وارد می‌کنیم. در سلول F13 از کاربرگ Sheet1 در فایل Time.xlsx تاریخ January 1, 2007 5:35 را وارد کرده‌ایم. البته این نشان‌دهنده ساعت 5:35  دقیقه در تاریخ یک ژانویه سال 2007 می‌باشد. اکسل به‌سرعت فرمت این عبارت را به 1/1/2007 5:35 تغییر داده و در نوار فرمول نیز می‌توانیم 1/1/2007 5:35:00 AM  را ببینیم.

اکسل چگونه محاسبات زمان‌دار را انجام می‌دهد؟

هنگامی‌که اکسل تفاوت‌هایی را در زمان محاسبه می‌کند، نتیجه بسته به فرمت استفاده شده در سلول نمایش داده می‌شود. تصویر 2-13 فرمت‌های زمانی مختلف در اکسل را نشان می‌دهد

تصویر 2-13 فرمت‌های زمانی در اکسل

در فایلTime.xlsx(تصویر 2-13 را ببینید) تفاوت میان ساعت 8:30PM و 8:30 AM را در سلول‌های F5 و H5 با فرمول =C3-C2 نشان‌داده‌شده. اگر فرمت را تغییر ندهید، اکسل فکر می‌کند که این زمان‌ها 12 ساعته هستند و همان‌طور که در سلولH5‌نشان داده شده زمان12:00PM را نشان می‌دهد. در بسیاری از موارد دوست داریم که اکسل زمان را به صورت 0.5 روزی نشان دهد( که با تقسیم به 24 می‌توانید این فاصله زمانی را به ساعت تبدیل کنید. برای اینکه اکسل در سلول ساعت را به صورت 0.5 روزی نشان دهد به سادگی فرمت سلول را به Number تغییر دهید.

در سلول F7 سعی شده با فرمول=D2-D3 زمان اولیه‌ای از زمان ثانویه کسر شود. ازآنجایی‌که فرمت سلول را تغییر نداده‌ایم، اکسل این عبارت ترسناک ##############  را نشان می‌دهد. اگر به‌سادگی فرمت سلول را به فرمت Number تغییر دهید(مثل سلول F8) اختلاف زمانی صحیح یعنی 0.5-  روز را به دست می‌آورید.

سلول‌های B17 وC17 زمان آغاز کار دو شغل و سلول‌های B18  و C18 زمان پایان کار آن دو را نمایش می دهند( تصویر 3-13)  را ببینید. اگربخواهیم حساب کنیم انجام این کار چقدر وقت می گیرد می‌توانیم به سادگی فرمول=B18-B17 را از سلول B19 به C19‌کپی کنیم و فرمت سلول را نیز به  فرمت Number تغییر دهیم.بنابراین کار اول 29.18 روز و کار دوم 29.97 روز وقت می گیرند تا تکمیل شوند.

تصویر 3-13 مشخص‌کردن زمان لازم برای تکمیل دو کار

 چگونه می‌توان کاری کرد که کاربرگ همواره زمان جاری را نمایش دهد؟

فرمول =NOW() تاریخ و زمان جاری را به ما می‌دهد. مثلاً در سلول G2‌( تصویر 4-13را ببینید)  از فایلی بنام Time.xlsx با وارد کردن فرمول =NOW() مقدار 1/9/2019 14:12   را به ما می‌دهد چرا که که من تصویر فایل را در ساعت 12:12 PM در تاریخ 9 ژانویه سال 2019 گرفته ام.( توجه کنید که اگر فایلی به نام Time.xlsx را ویرایش کنید، سلول G2 به شما تاریخ و زمان جاری را نشان می‌دهد) برای محاسبه زمان جاری همچنین می‌توانید در سلولH2 یا I2 فرمول =NOW()–TODAY()  را وارد کنید. سلول H2 فرمت شده تا زمان (2:12 PM) را نشان دهد درحالی که سلول I2 فرمت شده تا عددی را نشان دهد( مثل 0,52‌روز). این نشان‌دهنده آن حقیقت است که 2:19 PM  پنجاه و نه درصد بین نیمه شب یک روز و نیمه شب روز دیگر می‌باشد.

تصویر 4-13  استفاده از توابع NOW() ‌و TODAY()

 چگونه می‌توان از تابعTime برای خلق زمان استفاده کرد؟

تابع TIME دارای دستور زبان TIME(hour,minute,second) می‌باشد. تابع TIME باتوجه‌به ارقام داده شده برای ساعت، دقیقه و ثانیه، زمانی از روز را برمی‌گرداند. تابعTIME هیچوقت ارزشی فراتر از 24 ساعت را به ما نمی دهد.

در سلول A2(تصویر 1-13 را ببینید) فرمول=TIME(8,30,0) زمان 8:30AM را به ما نشان می‌دهد. در سلولA3 فرمول=TIME(20,30,0)  زمان 8:30PM را به ما می‌دهد، فرمول =TIME(15,10,30) زمان 3:13:30 PM‌را به ما می‌دهد. بالاخره توجه کنید که در سلول A5 فرمول =TIME(25,10,30) عدد 25 را به‌گونه‌ای در نظر می‌گیرد که انگار 24-25 باشد و بنابراین زمان 1:10:30AM را به ما می‌دهد.

البته اگر تعداد ثانیه‌ها اصلاً در سلول نشان داده نشدند بنابراین به کادر محاوره‌ای Format Cells به بخش Time category رفته و فرمت زمانی‌ای که ثانیه‌ها را نشان دهد را انتخاب کنید.

چگونه می‌توان با استفاده از تابعTIMEVALUE یک رشته متنی را به زمان تبدیل کرد؟

تابع TIMEVALUE دارای دستور زبان =TIMEVALUE(timetext)‌ می‌باشد که در اینجا  timetext رشته متنی است که زمانی را به فرمت معناداری نمایش می‌دهد. پس تابعTIMEVALUE زمان را به‌عنوان کسری بین 0 و یک به ما می‌دهد. (این بدان معنا است که تابع TIMEVALUE تمامی تاریخ‌های رشته متنی timetext را نادیده می‌گیرد) مثلاً در سلولA7 در فایلی بنام Time.xlsx(تصویر 1-13 را ببینید) فرمول =TIMEVALUE(“8:30”) عدد0.354166667 را به ما می‌دهد چراکه 8:30AM 35.4 درصد از کل زمان میان نیمه‌شب یک روز و نیمه‌شب روز بعد محسوب می‌شود.

چگونه می‌توان ساعت، دقیقه یا ثانیه را از یک‌زمان مشخص استخراج کرد؟

توابعHour، Minute و Second واحد زمانی مورد نظر را از یک سلول محتوای زمان استخراج می کنند. مثلا( همانطور که در تصویر 1-13 نشان داده شده) واردکردن فرمول =HOUR(A4) در سلولC5 به ما  زمان 15:00 را می‌دهد( 3:00 بعد از ظهر به زمان نظامی 15:00 حساب می‌شود) واردکردن فرمول=Minute(A4)  در سلول D5 به ما عدد 10 را می‌دهد و وارد کردن  فرمول =SECOND(A4) در سلول E5 به ما  عدد 30 را می‌دهد.

باتوجه‌به زمان شروع و پایان کار، چگونه می‌توان تعداد ساعت‌های کار انجام شده یک کارمند را مشخص کرد؟

در سلول‌های C10:C11(تصویر 5-13  را ببینید) زمان‌هایی را که جین و جک کارشان را شروع و به پایان رسانده اند درج شده است. می‌خواهیم ببینیم هریک از آنها چقدر کارکرده‌اند. مشکل اینجاست که جین کارش را روز بعد از شروع کارش به پایان رسانده است، بنابراین یک تفریق ساده تعداد صحیح ساعت‌های کاری آنها را به ما نخواهد داد. کپی فرمول:

=IF(D10>C10,(D10–C10)*24,24+(D10–C10)*24)  در سلول‌های C13 الی C14  پاسخ صحیح را به ما خواهد داد. البته توجه کنید که فرمت این سلول‌ها به فرمت عددی تغییر داده شده است. اگر زمان پایان کار بعد از شروع کار باشد، کم کردن زمان شروع از زمان پایان و ضرب کردن آن در عدد 24 به ما ساعت‌های انجام کار را به ما خواهد داد. اگر زمان پایان کار قبل از زمان شروع باشد بنابراین فرمول 24*(finish time–start time) به ما عددی منفی خواهد داد، اما اضافه‌کردن 24 ساعت همه چیز را درست می‌کند، البته با فرض آنکه انتهای شیفت کاری یک روز بعد بوده است؛ بنابراین جین 9 ساعت و جک 8.5 ساعت کارکرده است.

تصویر 5-13 محاسبه طول زمان انجام کار توسط کارمندان

 هنگامی‌که کل ساعت‌های کار کارمندی را جمع می‌زنم، بیشتر از 24 ساعت به دست نمی‌آورم، اشکال کار در کجا است؟

در سلول‌های C31:D35 در تصویر 6-13 تعداد ساعت‌های کاری یک کارمند در هریک از روزهای هفته کاری وی را درج کرده‌ایم (به فرمت h:mm). از فرمول=SUM(D31:D35)  در سلول D36 برای محاسبه تعداد کل ساعت‌های کاری وی در طی هفته استفاده شده است. اکسل رقم 14:48  را به ما می‌دهد. این رقم کاملاً اشتباه است. حالا هفتمین فرمت زمانی را (که عدد 38:48:00رانشان می‌دهد) را انتخاب می‌کنیم که به ما اجازه می‌دهد بیش از 24 ساعت را در محاسبات خود دخیل کنیم. پس از آن  جمع ساعت‌های کار شده در هر روز رقم صحیح ساعت‌های کارشده را به ما می‌دهد(38 ساعت و 48 دقیقه).

چطور می‌توان به‌آسانی  رشته‌ای  با فاصله زمانی منظم ایجاد کرد؟

فرض کنید پزشکی در فاصله زمانی8:00AM تا 5:00PM ویزیت‌هایی 20 دقیقه‌ای می‌پذیرد. چگونه می‌توان در ردیف‌های مختلفی لیستی از زمان‌های ویزیت متفاوت وارد کرد؟ برای انجام این کار به‌سادگی می‌تواند از خاصیت فوق‌العاده Autofill (تکمیل خودکار) اکسل استفاده کرد.(تصویر 7-13 را ببینید) برای انجام این کار دو زمان را (8:00AM‌و 8:20AM) ‌در سلول‌های L15 :L16 وارد می‌کنیم. حالا سلول‌های L15:L16 را انتخاب کرده و نشانگر ماوس را به پایین گوشه سمت راست سلولL16  ببرید تا علامت بعلاوه سیاه رنگی را ببینید. حالا نشانگر ماوس را پایین بکشید تا وقتی‌که عبارت 5:00 PM (آخرین زمان ویزیت پزشک) به شما نشان داده شود (در ScreenTip). حالا اکسل از سلول‌های L15:L16‌ (به‌درستی) حدس زده است که شما می‌خواهید زمان‌هایی با فاصله 20 دقیقه‌ای را وارد کنید. البته واردکردن عبارتMonday در یک سلول و عبارت Tuseday‌در سلول زیر آن و استفاده کردن از ویژگی Autofill به  شما رشته‌ای از روزهای  هفته یعنی دوشنبه، سه‌شنبه، چهارشنبه و… که در نهایت دوباره از دوشنبه شروع می‌شود را می‌دهد.  واردکردن تاریخ 2007/1/1 در یک سلول، 20117/1/2 در سلول دیگر، سپس انتخاب این دو سلول و استفاده از ویژگی Autofill به شما رشته‌ای از تاریخ‌ها مثل 2007/1/1، 2007/1/2 ، 2077/1/3 و به همین ترتیب خواهد داد.

تصویر 7-13 واردکردن دوره‌های مختلف زمانی

 چگونه می‌توان یک‌زمان ثابت(بدون تغییر) را در سلولی قرارداد؟

فرض کنید فایل اکسلی ایجاد می‌کنید و می‌خواهید که آن فایل همواره زمان دقیق ایجاد خودش را نمایش دهد. برای انجام این کار به‌سادگی در یک سلول خالی کلیدهای Ctrl+Shift+;(کنترل به‌اضافه شیفت به‌اضافه سمی‌کالن) را فشار دهید و این کار باعث می‌شود همواره زمان ثبت آن فایل در آنجا دیده شود. مثلاً همان‌طور که در تصویر 8-13 نشان‌داده‌شده، می‌توانید ببینید که این کاربرگ در ساعت 7:10AM ایجاد شده است.

تصویر 8-13 قراردادن یک‌زمان ثابت در سلول

مسئله‌های این فصل

– فرمولی بنویسید که زمان 18 ساعت بعد از زمان جاری را به ما بدهد.

– فایل Marathon.xlsx  زمان دوی ماراتون دونده‌ها را به ما می‌دهد. مسئله‌های زیر به این داده‌ها ارجاع می‌کنند. میانگین زمان هریک از دونده‌ها را محاسبه کنید.

1. جان چقدر سریع‌تر از جیل دویده است؟
2. هر دونده چند دقیقه را برای دویدن صرف کرده است ؟
3. هر دونده چند ثانیه را برای دویدن صرف کرده است ؟

– فایلی به نام Jobshop.xlsx به شما زمان شروع و تاریخ چندین شغل و زمان موردنیاز برای تکمیل هر شغل را  در اختیار ما قرار می‌دهد. زمان تکمیل هر شغل را مشخص کنید.