勤務時間の計算方法

質問

勤務時間の計算を、以下のようにやりたいのですが、可能でしょうか？

A1=出社時間
B1=退社時間
C1=1日の勤務時間

※昼休憩12:00～13:00と夜休憩18:00～18:30は休憩時間となります。

出社時間が日によって異なるため、どのように休憩時間を
引けばよいのか悩んでいます。

よろしくお願いします。

回答No.7

Yosha

まだ締め切られていないので、解決できてないのではと思い、実例を書いてみます。考え方は他の回答者様とほぼ同じです。

色々やってみましたが、解決する式を１つセルに入れようとすると、たとえIF文のネスト数を制限値の15以下になるような場合分けにしても、演算子や関数の数が制限値より多くなりどうしてもうまくいきません。

EXCELでは、多数の空いたセルがありますので、それを使わない手はありません。そうすることに抵抗を感じるかもしれませんが、セルにはなるべく簡単な式を入れて処理するようにした方がミスなどを防ぐのに効果的です。EXCELはこういった使い方をするように設計されていると思っています。

そこで、質問の勤務時間計算を実行するにあたり、ANo.6のroot16さんの言われている通り、問題なのは出社時間、退社時間が休憩時間内の場合の処理を含めると場合の数が増加しますので、右のほうの空いた列を２列ほど使い、出社時間、退社時間の休憩時ないの分をカットした出社時間、退社時間の列を作り、その列を対象として計算するようにします。

A列に出社時間、B列に退社時間、C列に1日の勤務時間、D列に休憩時間を除いた実勤務時間
ちょっと離れた、G列に休憩時間を外した出社時間、H列に休憩時間を外した退社時間を入れることにします。

先ず、G1セルに
=IF(A1<TIME(12,,),A1,
IF(A1<=TIME(13,,),TIME(13,,),
IF(A1<TIME(18,,),A1,
IF(A1<TIME(18,30,),TIME(18,30,),A1))))
を、H1セルに
=IF(B1<TIME(12,,),B1,
IF(B1<=TIME(13,,),TIME(12,,),
IF(B1<TIME(18,,),B1,
IF(B1<TIME(18,30,),TIME(18,,),B1))))
をコピペし、必要ならばスペースを取り除いてください。その後、下方にドラッグします。
G列、H列はもっと右の見えないところでも構いません。そうしたいときは、G列の前(左の)列に空列を必要な数、追加してください。そうすると式はEXCELが自動的に再計算してくれます。

次に、D1セルに
=IF(AND(G1<=TIME(12,,),TIME(13,,)<=H1,H1<=TIME(18,,)),TIME(12,,)-G1+H1-TIME(13,,),
IF(AND(TIME(13,,)<=G1,G1<=TIME(18,,),TIME(18,30,)<=H1),TIME(18,,)-G1+H1-TIME(18,30,),
IF(AND(G1<=TIME(12,,),TIME(18,30,)<=H1),TIME(12,,)-G1+TIME(18,,)-TIME(13,,)+H1-TIME(18,30,),H1-G1)
または
=IF(AND(G1<=0.5,13/24<=H1,H1<=0.75),0.5-G1+H1-13/24,
IF(AND(13/24<=G1,G1<=0.75,18.5/24<=H1),0.75-G1+H1-18.5/24,
IF(AND(G1<=0.5,18.5/24<=H1),0.5-G1+0.75-13/24+H1-18.5/24,H1-G1)
をコピペし、必要ならばスペースを取り除いてください。その後、下方にドラッグします。

これでうまくいくと思います。ただし、質問の内容から推察して、夜中の勤務はないものとしています。
各式の内容は、難しくないので自分で勉強して理解してください。先々のためにも・・・。

回答No.6

root16

場合分けすると、15通りもあり簡単ではありません。
午前出社：5通り
昼休み出社：4通り
午後出社：6通り（含む夕方休み出社、夕方休み後出社）
IFの条件式では残念ながらネストしきれないようです。
条件をややこしくしているにのは
出社時間、退社時間が休憩時間内の場合です。
この場合のために出社時間（修正）、退社時間（修正）の列
C1、D1を作り、E1セルにC1、D1を使った条件式を
入れたほうが良いと思います。

回答No.5

noname#176215

#3の者です。ひとつ抜けてました。

夜休憩時間から 退社時間までの時間
=MAX(退社時間*24-18.5,0)

回答No.4

Bronco7

C1のセルにくそ長いIf文の式を入れるか
自動ってのならオリジナル関数を作るしかないかと思いますよ。

A1が　13:00　以前なら　1時間引く
B1が　18:30　以降なら　30分引く

出社時間が日によって異なるといっても
上記の条件は変わらないと思いますけどね？

回答No.3

noname#176215

条件が小出しになりそうな気がすごくするので 回答も小出しします。
計算を午前と午後に分ければいいのでは？

あとひとつ。時間計算をする時は 演算誤差対策をする必要があります。

出社時間から 12:00までの時間
=MAX(12-出社時間*24,0)/24

13:00から 退社時間までの時間
=MAX(MIN(退社時間*24,18)-13,0))/24

回答No.2

takewan

簡潔明瞭に、以下の式をC1に入力してみてください。
その際、書式は標準ではなく時間表示にしておいてくださいね。

=IF(AND(A1<TIME(12,0,0),B1>TIME(18,30,0)),B1-A1-TIME(1,30,0),IF(AND(A1<TIME(18,0,0),B1>TIME(18,30,0)),B1-A1-TIME(0,30,0),IF(AND(A1<TIME(12,0,0),B1>TIME(13,0,0)),B1-A1-TIME(1,0,0),B1-A1)))

回答No.1

qqvx4qk9k

休憩分を普通に勤務時間から引けばいいですよ。

かんたんです。

補足

ご回答ありがとうございます。

そうなんです。
休憩時間を入れるセルを用意して、それを入力し、引けばよい話なのですが、

出社時間と退社時間だけ入れて、その中に12:00～13:00もしくは18:00～18:30が
含まれていた場合は、自動で引いてくれる、、、

なんていう式は、やはり難しいでしょうか。