Excelで勤務時間を計算する式について

質問

会社の勤務報告書が下記画像の形式で手入力しております。

今回、勤務時間を関数を用いた式で算出できないかと思い、
頭を悩ませております。

悩んでいるポイントは、
　・開始時間と終了時間が数値になっている
　・終了時間が四捨五入で記述されている（厳密には６分単位）
という点です。

休憩時間は12:00～13:00と17:40～18:10となっておりますが、
こちらはIF文を用いて処理できそうです。

算数や数学が得意な方や、勤務表をすでに関数化してらっしゃる方、
どうかお力添え頂けないでしょうか。

よろしくお願いいたします。

回答No.2

keithin · ベストアンサー

Ａ２に開始時刻を９００のように数字で記入
Ｂ２に終了時刻を１７４０のように数字で記入
暫定的に「６時から１２時」「１３時から１７４０時」「１８１０時から２２００時」の勤務時間を数える事にして
=IF(COUNT(A2:B2)<2,"",ROUND((MAX(MIN("12:0",TEXT(B2,"0!:00")),"6:0")-MIN("12:0",MAX("6:0",TEXT(A2,"0!:00")))+MAX(MIN("17:40",TEXT(B2,"0!:00")),"13:0")-MIN("17:40",MAX("13:0",TEXT(A2,"0!:00")))+MAX(MIN("22:00",TEXT(B2,"0!:00")),"18:10")-MIN("22:00",MAX("18:10",TEXT(A2,"0!:00"))))/"1:0",1))
といった具合の計算式になります。

＃大層判りにくいですが
MAX(MIN("12:0",TEXT(B2,"0!:00")),"6:0")-MIN("12:0",MAX("6:0",TEXT(A2,"0!:00")))
MAX(MIN("17:40",TEXT(B2,"0!:00")),"13:0")-MIN("17:40",MAX("13:0",TEXT(A2,"0!:00")))
MAX(MIN("22:00",TEXT(B2,"0!:00")),"18:10")-MIN("22:00",MAX("18:10",TEXT(A2,"0!:00"))))
が、それぞれの時間帯にかかる正しい勤務時間数です
たとえば１３時勤務開始などのパターンでも検証してみます。
＃応用すると、たとえば深残の時間数とかもこのパターンで計算できます。

＃なお
＞終了時間が四捨五入で記述されている（厳密には６分単位）

ご説明とご質問に掲示された事例が合っていません。どこに間違いがあるのかご相談からは判断できないので、自力で適切に修正してご利用ください

＃もう一つ
最初の例の７時間４０分の勤務時間は、１時間単位の数字に直すと７．６６６６時間なので、少なくとも７．６という答えにはなりません。
それとも？四捨五入という説明から間違いで６分単位に切り捨てでしょうか。ご自分で適切に修正なさってください。

お礼

ご回答ありがとうございました。
ご指摘の通り、四捨五入ではなかったですね・・・
時刻や数字の計算に弱いもので、なんとなく周りに合わせて入力しておりました。
私としては非常に難しい式で、
理解するには時間がかかりそうですが、がんばりたいと思います。

回答No.6

Nouble

当方映像が見れません、
的外れな場合は誤用者ください。

B5セルに始業時間。C5セルに終業時間が入るものとします。
数値は4桁で0000から2359までの間、但し十の位には7、8、9、は入らないものとします。

まず、B5:D5を選択し
セルの書式設定ウインドウを開きます。
今回設定するのは表示形式です、
一番左のタブですね。
表示形式を設定するウインドウ中には
幾つかの内ウインドウ(？)があると思いますが
まずはその中、左の内ウインドウからです。
幾つか羅列されているものがあると思いますが、
その中の一番下、
「ユーザー定義」をクリックで選択してください。

次は右やや上方にある、横に細長い欄です。
ここには直接文字入力ができるようになってますので
###0;###0;0000;@
と、入力してOKをクリックしてください。
次に
C5に
=INT(E5/60)*100+INT((MOD(E5,60)+6-$B$2)/6)*6+IF(MOD(E5,60)+6-$B$2>60,40,0)

E5に
=CHOOSE(RANK(C5,{1200;1300;1740;1810},1),D5,180,D5-60,460,D5-90)/60)

D5に
=(INT(C5/100)*60+MOD(C5/100,1))*100-(INT(B5/100)*60+MOD(B5/100,1)*100

B2には切り上げ開始したい数を
0～5の範囲で入れてください
ここに仮に4を入れたとすると
6n+4分と6m+5分が6(n+1)分になり
6n+1分、6n+2分、6n+3分が6nになります。
(※nは9以下の自然数)

これらの式を統合するとC5単体で作業列なしでもかけるのですが、
同一計算を幾度となくさせること、
メンテナンス性が下がることを考慮して
今回はこうしました。

解説を求められる場合は
その旨、お申し付けください。

Excelにかけずに書いたので
少し確実性がないのですが、
動作するものと思います。

以上ですが、
これでお役に立ててたなら幸いです。

お礼

丁寧にご回答頂きありがとうございました。
現在も優先する仕事などの事情から、
ご回答内容を試すことができておりません。
ただ、皆さまの回答をみている限り、私には荷が重そうです・・

回答No.5

kagakusuki

>終了時間が四捨五入で記述されている（厳密には６分単位）
>という点です。

との事ですが、それは少々おかしいのではないかと思います。
　その考え方では、例えば、開始時間が13:46で終了時間が19:10の場合、終了時間は0.1時間単位で四捨五入されて、19:12として扱われますから、勤務時間は

17:40-13:46+19:12-18:10=4時間56分=4.933333・・・・時間

になってしまい、0.1時間未満の端数が出て来てしまいます。
　ですから、開始時間や終了時間を0.1時間単位で四捨五入するのではなく、
開始時間や終了時間はそのままの時間で計算してしまい、その結果求められた勤務時間の値の方を0.1時間単位で四捨五入されるか、
或いは、開始時間に関しては、開始時間そのものを0.1時間単位で四捨五入するのではなく、次の休憩時間までの残りの時間を0.1時間単位で四捨五入し、同様に終了時間に関しては、終了時間そのものを0.1時間単位で四捨五入するのではなく、直前の休憩時間の終わりから経過した時間を0.1時間単位で四捨五入されるか、
のどちらかにされた方が宜しいのではないかと思います。
　その場合、勤務時間を求める方法は以下の様になります。

　今仮に、開始時間がA2セルに入力されていて、終了時間がB2セルに入力されていて、勤務時間をC2セルに表示させるものとします。
　その場合、C2セルには次の関数を入力されると良いと思います。

【開始時間や終了時間はそのままの時間で計算してしまい、その結果求められた勤務時間の値の方を0.1時間単位で四捨五入する場合の関数】

=IF(IF(AND(COUNT($A2,$B2),$A2>=0,$B2>$A2,$B2<10000),AND($A2=INT($A2),MOD($A2,100)<60,$B2=INT($B2),MOD($B2,100)<60),FALSE),ROUND(((MIN((INT($B2/100)&":"&MOD($B2,100))+0,"12:00"+0)-(INT($A2/100)&":"&MOD($A2,100)))*($A2<1200)+(MIN((INT($B2/100)&":"&MOD($B2,100))+0,"17:40"+0)-MAX((INT($A2/100)&":"&MOD($A2,100))+0,"13:00"+0))*($A2<1740)*($B2>1300)+((INT($B2/100)&":"&MOD($B2,100))-MAX((INT($A2/100)&":"&MOD($A2,100))+0,"18:10"+0))*($B2>1810))*24,1),"")

【開始時間に関しては、開始時間そのものを0.1時間単位で四捨五入するのではなく、次の休憩時間までの残りの時間を0.1時間単位で四捨五入し、終了時間に関しては、終了時間そのものを0.1時間単位で四捨五入するのではなく、直前のの休憩時間の終わりから経過した時間を0.1時間単位で四捨五入する場合の関数】

=IF(IF(AND(COUNT($A2,$B2),$A2>=0,$B2>$A2,$B2<10000),AND($A2=INT($A2),MOD($A2,100)<60,$B2=INT($B2),MOD($B2,100)<60),FALSE),ROUND((MIN((INT($B2/100)&":"&MOD($B2,100))+0,"12:00"+0)-(INT($A2/100)&":"&MOD($A2,100)))*($A2<1200)*24,1)+ROUND((MIN((INT($B2/100)&":"&MOD($B2,100))+0,"17:40"+0)-MAX((INT($A2/100)&":"&MOD($A2,100))+0,"13:00"+0))*($A2<1740)*($B2>1300)*24,1)+ROUND(((INT($B2/100)&":"&MOD($B2,100))-MAX((INT($A2/100)&":"&MOD($A2,100))+0,"18:10"+0))*($B2>1810)*24,1),"")

　後それから、質問者様の添付画像の例では900～1740の場合の勤務時間が7.6時間になっていますが、1740を0.1時間単位で四捨五入しますと17:42になりますから、勤務時間は7.6時間ではなく、7.7時間になるのではないでしょうか？

お礼

ご回答ありがとうございます。
ご指摘の通り、現在の入力時間も正確ではないかもしれません。
周りが同様に記述しているのと、
よく記述を理解できないまま算出方法の式作成を求められ、
私としてもよくわからないまま急ぎで質問してしまい、申し訳なく思います。

回答No.4

K Kazz（@JazzCorp）

休息時間帯の設定があるサンプルはあまりない。
ココのやり方が素直で、応用（早出等）もできるでしょう。

勤務時間の計算例(関数)
http://www.eurus.dti.ne.jp/yoneyama/Excel/nyumon/kinmu/index.html

お礼

ご回答ありがとうございました。
確かにご提示頂いたリンク先のようなページで
しっかり勉強しつつ式を考えられればよいのですが、
どうしても時間が割けず、今回は式が例示されればと考えております。

回答No.3

MarcoRossiItaly

できれば、Excel における通常の時刻の入力方法を採用することをお勧めしたいと思います。次のようにします。

「900」が午前 9 時を表すのであれば、「9:00」とタイプします。これは、確定と同時に午前 9 時の「シリアル値」に変換されてセルに入力され、その値は「0.375」という数値なのですが、見かけは「9:00」と表示されています。「0.375」をセルの書式でそのように見せているだけなのです。

「AM」や「PM」はタイプしなくて大丈夫です。「17:40」と打てば、数式バーでは自動的に「5:40:00 PM」と表示されます。

つまり、「9:00」とタイプした後に、「900」に見えるような書式に変更してあげればいいですね。具体的には、B・C 列のデータ入力したセル範囲を選択して、右クリックなどから「セルの書式設定＞表示形式タブ＞ユーザー定義＞種類ボックス」に「hmm」と入力するだけです。見かけは「900」ですが実態は「0.375」であることが、書式を「標準」などに変更すれば確認できます。

こうしておくと何がいいのかというと、時間の計算がとても簡単になるということです。

シリアル値は、24 時間で「1」になるような値に定められています。1 × 24 = 24 であるのと同様に、0.375 × 24 = 9 が成り立ちます。つまりシリアル値に 24 をかけると、時間数に変換されるのです。したがって、

=24*(c2-b2)

※「24 倍する」代わりに「1:00 のシリアル値で割る」という計算をしても、
　同じ結果が得られます。「=(c2-b2)/"1:00"」という具合に。

というふうに時間差が計算できます。ここまで、関数は不要ということです。

ただ、これだけだと休憩時間が考慮されてませんね。ある期間と他の期間との「重なり」は、「MAX(,MIN-MAX)」という形の数式で表されます。

=24*(c2-b2-max(,min(c2,"13:00")-max(b2,"12:00"))-max(,min(c2,"18:10")-max(b2,"17:40")))

D 列にこの数式を入力したら、そのセルの書式は、上でユーザー定義の時刻の書式を設定したのと同様にして、ボックスに「0.0」と入力するなどしてください。時刻の書式だと、「7.66…」が「16:00」とか表示されてしまったりします。なお D 列が「標準」の書式の場合は、B 列や C 列を編集していると、その右の D 列も書式が自動変更されてしまったりしますので、ご注意ください。

お礼

ご回答ありがとうございました。
確かにきちんと時刻の形式で入力されるべきものですが、
現状全員が図の形式で入力しており、
浸透させるには少し時間がかかると思われますが、
検討はしたいと思います。

回答No.1

tom04

こんばんは！
本来であれば入力時にシリアル値のして入力する方が後々のコトを考えるとよいと思うのですが、
すでのお示しのようなデータになっている場合は、
一気に！となると長い数式になってしまうと思います。
そこで一例です。

↓の画像のように「休憩時間を無視した勤務時間」（D列）と
「休憩時間」（E列）を別途計算する列を設けておきます。

D2セル（←セルの表示形式はユーザー定義から [h]:mm）に
=SUBSTITUTE(B2,RIGHT(B2,2),":"&RIGHT(B2,2))-SUBSTITUTE(A2,RIGHT(A2,2),":"&RIGHT(A2,2))

E2セル（←こちらのセルの表示形式も　[h]:mm　）に
=IF(B2>1810,"1:30"*1,IF(B2>1300,"1:00"*1,0))

という数式を入れ、両列ともオートフィルで下へコピーしておきます。

C2セル（←セルの表示形式は「標準」）に
=HOUR(D2-E2)+FLOOR(MINUTE(D2-E2)/6,1)/10
という数式を入れオートフィルで下へコピーで
画像のような感じになります。

尚、エラー処理はしていません。m(_ _)m

お礼

迅速なご返答ありがとうございました。
仕事上、勤怠の修正どころではない状態になってしまい、
回答頂いた皆さまへのお礼が遅くなり申し訳ございません。

さて、複数列にわけて式を作成するという方法ですが、
１つの式に収めることしか考えていなかったもので、
目からうろこでした。
現在まだ式の内容と処理についてほとんど把握できておりませんが、
少しずつ勉強させて頂きたく思います。