Ｅｘｃｅｌで労働時間を集計～フレックス制

いつもお世話になっております。
労働時間を管理するために分刻みで出社時間から退社時間を入力するだけで
労働時間を算出するための関数式をご教示下さい。
一日の労働時間を算出することと
８時間超過分の普通残業時間
２２時以降の深夜業時間の算出が可能で一番効率のいい式を
是非教えてください。宜しくお願い致します。

No.1 回答者： hg3 回答日時： 2014/02/16 01:10

A1のセルに出社時間、B1のセルに退社時間を入力するとしましょう。

入力する数値を　○○：○○　という形式で入力して下さい。
午前7時30分なら、7:30、午後6時なら　18：00　と言う感じです。

一日の労働時間は、= B1-A1-(休憩時間）/24 　　の式で計算できます。
8時間超過分は、　=IF(B1-A1>(休憩時間+8)/24,B1-A1-(休憩時間+8)/24,0)
22時以降の深夜業時間は、=IF(B1-22/24>0,B1-22/24,0)
となります。

尚、休憩時間は1時間なら1、1時間半なら1.5というように時間単位で数字を入れてください。
もし、休憩時間が一定でないなら、C1のセルに休憩時間を時間単位で入力し、式中の休憩時間をC1としても良いです。

また、これらの式を入力するセルの表示形式は、「時刻」の　「○○：○○」にしておきます。

この回答へのお礼

ご回答ありがとうございました。とてもシンプルでありがたかったです。
参考にさせていただきます。
またの機会がございましたら、宜しくお願い致します。

お礼日時：2014/02/18 22:24

No.2 ベストアンサー 回答者： Cupper-2 回答日時： 2014/02/16 03:25

フレックス制と言うことですので、コアタイムがどこに設定されているのかが問題になるんですけど・・・まあ、いいでしょう。

基本的に

　退社時間－出社時間

で勤務時間は計算できます。
他に、前のかたも回答しているように休憩時間を差し引くかどうかですね。

ここでちょっとしたテクニックなんですけど、時間を示す文字列を計算に使うとき
Excelは勝手にシリアル値に置き換えて計算をするという性質を利用します。

８時間を超えた分は素直に計算すればいいでしょう。
ただし、時間の計算で結果がマイナスの値になるとエラーになりますので、次のように計算すると良いでしょう。

　=MAX("8:00",(退社時間)-(出社時間)-(休憩時間))-"8:00"

これなら就業時間が8時間に達しない場合は結果として”０：００”が返ってきます。
同様に22時を超過した時間に対しても

　=MAX("22:00",(退社時間))-"22:00"

で求められます。

日付をまたぐ場合は、日にちも含めて計算させましょう。

　=MAX("8:00",(退社時間)-(出社時間)-(休憩時間))-"8:00"

この「退社時間」と「出社時間」に日にちを含めるんです。
　出社時間が「10:00」、退社時間が翌日「2:00」の場合、
　出社時間を「2014年2月14日 10:00」
　退社時間を「2014年2月15日 2:00」
とすればそのまま計算できます。

コアタイム１０：００～１５：００に対して不足する時間があるかを計算したいのでしたら
まずは出社時間
　=MAX("10:00",(出社時間))-"10:00"
次に退社時間
　="15:00"-MIN("15:00",(退社時間))
でそれぞれ求められます。
ここでは日にちを含めない時間で計算しましょう。
　=MAX("10:00",TIMEVALUE(HOUR(出社時間)&":"&MINUTE(出社時間)))-"10:00"
　="15:00"-MIN("15:00",TIMEVALUE(HOUR(退社時間)&":"&MINUTE(退社時間)))
などとTIMEVALUE関数を使うなど工夫してみましょう。

　※自分は横着ですので、シリアル値を前提にMOD関数でやってしまいますけどｗ
　　=MAX("10:00",MOD((出社時間),1))-"10:00"
　　="15:00"-MIN("15:00",MOD((退社時間),1))
　　とか♪

あと、計算結果のセルは時間表示の書式に設定しておく必要がありますが、
TEXT関数を
　=text(時間計算の数式,"hh:mm")
このように加えて、強制的に時間表示にするという手もあります。


そんなわけで効率がいいかどうかは質問者さんが利用される環境で変わってくると思いますので
質問者さん自身でこれらを前提に組み上げることをお勧めします。

この回答へのお礼

ご回答ありがとうございました。コアタイムも実は気になってたので
とてもありがたかったです。
参考にさせていただきます。
またの機会がございましたら、宜しくお願い致します。

お礼日時：2014/02/18 22:25