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

برای مثال در جدول ذیل اطلاعات ورود و خروج پرسنل در دو شیفت روز کار وشب کار تاریخ ۲۱/۵/۱۳۹۸ آورده شده است. می خواهیم با استفاده از فرمول های اکسل، ساعت کارکرد هر یک از افراد را محاسبه نماییم.

 

محاسبه ساعت کارکرد در اکسل

به منظور حل این مسئله باید، ساعت ورود از ساعت خروج کسر شود، لذا کافی است درون سلول D2 فرمول ذیل را وارد نمود و برای کلیه سلولهای D2 تا D5 اعمال کرد.

=C2-B2

با اعمال این فرمول برای کلیه سلولهای محدوده D2 تا D5، نتیجه به صورت ذیل به دست می آید.

 

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

برای رفع این موضوع میتوان یکی از روشهای ذیل را انجام داد:

 

روش اول محاسبه ساعت کارکرد در اکسل : اصلاح ساعت خروج

همانطور که مشاهده میشود فرد سوم ساعت ۸:۰۵ بعد الظهر به سر کار آمده است و ساعت ۱:۵۳ صبح روز بعد از محل کار خارج شده است، به منظور نشان دادن آن که عدد مربوط به ساعت خروج در روز بعد اتفاق افتاده است، میتوان مقدار ثبت شده درون آن سلول را با یکی از روشهای ذیل با عدد ۱ جمع نمود  ( عدد  ۰٫۰۷۸۵۶۸۲۵۵۳۷۸۸۰۷۹ معادل با ساعت ۱:۵۳ می باشد)

=TIME(1,53,0)+1

= ۰٫۰۷۸۵۶۸۲۵۵۳۷۸۸۰۷۹+۱

با انجام اصلاحات فوق در ظاهر فایل تغییری دیده نخواهد شد ولی نتیجه به صورت ذیل اصلاح خواهد شد.

 

به منظور مشاهده تاثیر این کار بر روی داده ها، کافی است اطلاعات ساعت ورود و خروج را انتخاب کرده و فرمت آن را بر روی تاریخ و ساعت قرار دهیم تا نتیجه مشابه شکل ذیل حاصل شود.

 

همانطور که در شکل فوق مشخص می شود تاریخ ثبت شده در تمامی سلولها به غیر از سلول اصلاح شده برابر با ۱/۰/۱۹۰۰ می باشد و در سلول اصلاح شده تاریخ به صورت ۱/۱/۱۹۰۰ به معنی یک روز بعد از سایر تاریخ ها می باشد.

روش اول محاسبه ساعت کارکرد در اکسل : اصلاح فرمول

در روش دیگر میتوانیم با اصلاح فرمول نتیجه را اصلاح کرد به گونه ای که به جای فرمول قبلی از فرمول ذیل استفاده کنیم.

=IF(C2>B2,C2-B2,1+C2-B2)

در صورت اعمال این فرمول برای محدوده D2 تا D6 نتیجه به صورت شکل ذیل به دست می‌آید.