excel関数 変動する休憩時間等を計算する

excel関数で勤務表を作っています。

基本実労働時間は7時間(拘束時間8時間)

拘束時間が8時間〜それ以上であれば実労働時間には休憩時間1時間を差引いた時間を。
残業時間には7時間超過分の時間を表示。

出勤又は退勤が空白ならば実労働時間、残業時間には空白を表示。

拘束時間が7時間〜6時間ならば、休憩時間30分を差引いた時間を実労働時間へ表示。

拘束時間が5時間以下ならば、実労働時間はそのまま表示。

と言う関数を作りたいのですが、どうすれば良いのでしょうか？

出勤L5 退勤M5 実労働時間N5 残業時間O5 休憩時間1時間O37 休憩時間30分M37

よろしくおねがいします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2022/03/16 19:13

こんばんは

関係する時刻は全てシリアル値とし、セルの書式は「[h]:mm」などになっているものと仮定します。


＞出勤L5 退勤M5 実労働時間N5 残業時間O5 休憩時間1時間O37 休憩時間30分M37
O37とM37の意味がわからないので、そちらは省きました。

N5セルに
=IF(COUNTBLANK(L5:M5)>0,"",M5-L5-IFS(M5-L5>7/24,2,M5-L5>1/4,1,1,0)/48)

O5セルに
=IF((N5<>"")*(N5>7/24),N5-7/24,"")

ではいかがでしょうか？

この回答へのお礼

回答ありがとうございます。
大変参考になりました！

お礼日時：2022/03/16 21:40

No.2 回答者： 銀鱗 回答日時： 2022/03/16 19:17

普通にセルが時間入力のセルが空白でないかを調べて、空白なら空白を返し、空白でなければ引き算などで勤怠の時間を計算した数式を選択するIF関数を使えば良いだけだと思います。

・・・

まずは時間入力のセルが空白なら空白を返すIF関数を作ってみましょう。
これは簡単ですよね？

ではつぎに、勤怠の時間の計算。
休憩時間が固定であれば、
　１．休憩時間までの勤務時間
　２．休憩時間後の勤務時間
　３．残業時間
をそれぞれ求めて、あとは足し算して勤務時間を表示、残業時間を表示するようにすれば良い。

自分はこのような計算をするとき、MIN/MAX関数を使います。
考え方は……
　休憩時間開始の時間と、退社時間を比較して、小さいほうから出社時間を引くことで出勤から休憩時間までの勤務時間を求める。
……です。

基本の考えでは
　MIN("12:00",退社時間)-出社時間　
こうなります。
ただ、出社時間が12:00よりも後のケースも考慮して、
　MIN("12:00",退社時間)-MIN("12:00",出社時間)
とします。
（これで12:00より後に出社した場合は、12:00までの勤務時間はゼロになります）
さらに、出社時間が朝8:30始業に対して6:00なんて場合を考慮すると
　MIN("12:00",退社時間)-MIN("12:00",MAX("8:30",出社時間))
とかになります。
（早出時間外対策です）

質問のケースでは休憩時間がセルに入力されているようなので、そのセルを参照すればよいでしょう。
　MIN(O37,退社時間)-MIN(O37,MAX("8:30",出社時間))
など。

このようにMIN/MAX関数を組み合わせてそれぞれの時間を求めます。
面倒ですけど、考え方さえ間違わなければ、自力でできるのではないでしょうか。
　基本パターン　|←─→|12:00|←─→|
　パターン１　　|←──→| 休 憩 |←→|
　パターン２　　|←─→| 休 憩 |←──→|
などと図を描いて考えると良いでしょう。

・・・余談・・・

Excelでは数値と見なされる文字列は数値として扱います。
ですので時間を "12:00" などと文字列を記入した場合、Excel内部では数値の「シリアル値」に変換されて計算されます。
それに……この方が見て分かりやすい。

あと、質問者さん的には、そのものズバリの数式を書いてくれることを期待していると思うのですが、
ここは「代わりに数式を作ってください」と依頼する場所ではなく、
自力で数式を作れるようになるためのアドバイスをもらう場所です。
代わりに作ってもらっても、ちょっと仕様が変わっただけで使い物にならなくなります。
自力で作れるようになれば、自分がその場で修正できます。

この回答へのお礼

ありがとうございます。大変参考になりました！

お礼日時：2022/03/16 21:39