時間の計算

質問

勤務時間を管理する為に計算式を作りたいのですが、よく分からなかったので質問いたします。

以下の条件に当てはまる様な式でお願いします。

例えば
・Ａ1に出勤時刻、Ｂ1に退勤、Ｃ1に通常勤務時間、Ｄ1にに残業を表示させるとした場合で、昼休みは1時間（12：00~13：00固定）引きます。
・出勤時刻は8：00とは限らず、10：00や午後～でも当てはまる様にしたいので、昼休みの時刻を固定して引きたいのです。
・残業時間とは、通常勤務時間が8時間を超えた場合に適用したいと思います。

以上でＣ1とＤ1に入る計算式を知りたいので、宜しくお願いします。
（不明な点がありましたら追加で投稿します。）

回答No.7

don9don9 · ベストアンサー

No.3です。
補足ありがとうございます。
では以下でどうでしょうか。

C1の式が
=IF(OR(AND(A1<="12:00"*1,B1<="12:00"*1),AND(A1>="13:00"*1,B1>="13:00"*1)),IF(B1-A1>="8:00"*1,"8:00"*1,B1-A1),IF(B1-A1-"1:00"*1>="8:00"*1,"8:00"*1,B1-A1-"1:00"*1))

D1の式が
=IF(OR(AND(A1<="12:00"*1,B1<="12:00"*1),AND(A1>="13:00"*1,B1>="13:00"*1)),IF(B1-A1>="8:00"*1,B1-A1-"8:00"*1,"0:00"*1),IF(B1-A1-"1:00"*1>="8:00"*1,B1-A1-"9:00"*1,"0:00"*1))

お礼

度々回答有難う御座います。

時間がないので、数式内の意味は考えずに使用してみました。
余計なスペースを使わずに計算が出来るのでとてもいい式だと思います。

今後もこの式を使用したいと思います。
有難う御座いました。

回答No.9

A88No8

＃８のA88No8です。

よく考えてみると退勤時刻12:00越え13:00未満の場合の処理も必要のようですね～(-。-;

「○」印は、適切な計算処理、「～時刻」は出勤時刻の正規化が必要、「時刻～」は退勤時刻の正規化が必要。
　　　　　　　　　　　　　退勤時刻12:00以下 | 退勤時刻12:00越え13:00未満 | 退勤時刻13:00以上
出勤時刻8:00未満　　　　　　　　8:00～　　 | 　　　　～12:00　　　　　　 | 　　　　○
出勤時刻8:00以上12:00以下　　　　○　　　　 | 　　　　～12:00　　　　　　 | 　　　　○
出勤時刻12:00越え13:00未満 　　Error 　　　 | 　　　Error 　　　　　　　　 | 　　　　13:00～
出勤時刻13:00以上　　　　　　　Error 　　　 | 　　　　Error 　　　　　　　 | 　　　　○

お礼

回答有難う御座います。

おっしゃるとおりで、上記の時間帯の計算方法が分からず質問させて頂いておりました。

以下の回答者でNo,4及びNo,7さんの回答で十分満足できる回答だと思いましたので、今回はこの辺で締めさせて頂きます。

多数の回答及び意見、大変有難う御座いました。
今後とも宜しくお願いいたします。

回答No.8

A88No8

mdyさん、みなさん、こんにちは

mdyさんの要求されていることを表にしてみました。
出勤時刻のとらえ方で大分変わるような気がします。
１セルでは、厳しいような気がしますけど出来ますか?

　　　　　　　　　　　　　退勤時刻13:00以下 | 退勤時刻13:00以上
出勤時刻8:00未満　　　　　　　　8:00～ 　　 | 　　　　○
出勤時刻8:00以上12:00以下　　　　○　　　　 | 　　　　○
出勤時刻12:00越え13:00未満 　　Error 　　　 | 　　　13:00～
出勤時刻13:00以上　　　　　　　Error 　　　 | 　　　　○

回答No.6

web2525

＞昼休みは1時間（12：00~13：00固定）引きます。

この文面は？
12:00出勤　20:00退出の場合、勤務時間は7時間?8時間?
どちらでしょうか
13:00出勤　20:00退出の場合、勤務時間は休み時間無しの計算で7時間
の計算でいいのかな?

お礼

>13:00出勤　20:00退出の場合、勤務時間は休み時間無しの計算で7時間
の計算でいいのかな?

はい。12:00～13:00の時間帯は労働時間に計算しないという事です。

回答No.5

asadome

ええっと、昼休みの途中から出勤した場合も、昼休みは固定ということで良いんでしょうか？
また、同様に昼休みの途中で退勤した場合もそうなんでしょうか？
（例えば、12:30に出勤した場合、昼休みは12:30-13:00の間でしょうか？）

お礼

おっしゃるとおりです。

12:00~13:00は固定で考えております。

No,3さんの補足欄をご覧下さい。

回答No.4

hallo-2007

この手の管理はこれからも条件が変わったりするので、作業列で入れておいて方が
わかりやすいと思います。
例えば
　A　　　B　　C　　　　D　　　　　E　　　　 F
出勤　退勤　午前勤務　午後勤務　通常勤務　残業
8:00 19:00
C2　=MIN("12:00",B2)-MIN(A2,"12:00")
D2　=MAX(B2,"13:00")-MAX(A2,"13:00")
E2　=MIN(C2+D2,"8:00")
F2　=MAX(C2+D2-E2,0)

お礼

回答有難う御座いました。

早速使用させて頂きましたところ、私の理想の式が正しくこれでしたので大変感謝しております。

今回は回答していただき、有難う御座います。
皆様も今後とも宜しくお願い致します。

回答No.3

don9don9

補足要求です。

・出勤時刻または退勤時刻が12:00～13:00の間の場合、昼休みはどう計算しますか？
例えば8:00出勤12:30退勤、12:30出勤21:30退勤といった場合です。
入力の仕方でそれは回避する（昼休み中に帰った人は12:00退勤扱い、
昼休み中に来た人は13:00出勤扱いにする）のであれば
これは考えなくていいのですが。

・退勤時刻が夜の12時を越えることはありますか？
例えば14:00出勤1:00退勤、のようなケースはありえますか？

お礼

回答有難う御座いました。

上記の補足、大変参考になります。
今後は上記についても注意して質問するように致します。

また指摘がありましたら宜しくお願いします。

補足

補足要求有難う御座います。

回答者の例題の件に関しましても、基本12:00~13:00内は計算外ということにしておきます。例えば12:30出勤の場合でも、13:00扱いにしたいと思います。

退勤時刻についても、その日を超えることは想定しておりません。
ですので、上記ケース2件は適用外でお願いします。

回答No.2

nattocurry

作業列を設ければ、解りやすい式になるんですけど、一発で求めたいなら、

C1に
=IF(B1-A1-IF(A1<1/24*13,1/24*13-A1)+IF(A1<1/24*12,1/24*12-A1)<1/24*8,B1-A1-IF(A1<1/24*13,1/24*13-A1)+IF(A1<1/24*12,1/24*12-A1),1/24*8)

D1に
=IF(B1-A1-IF(A1<1/24*13,1/24*13-A1)+IF(A1<1/24*12,1/24*12-A1)<1/24*8,0,B1-A1-IF(A1<1/24*13,1/24*13-A1)+IF(A1<1/24*12,1/24*12-A1)-1/24*8)

と書けば良いです。

退勤時間が13時以降であるという前提です。

書式は、適当に設定してください。

お礼

早速の回答有難う御座います。

かなり完成度が高くて感動しましたが、質問の条件には書かなかったので申し訳ありませんが、出退勤時刻が午前中（例：8:00~10:00等）の場合にでも対応できる式は作れるでしょうか。
どうぞ宜しくお願いします。

お礼欄に追加質問しまして申し訳在りません。
上記式でも大変参考になり十分役に立ちます。

補足

退勤時間が午前中である可能性も含めてお願いしたいです。

書き忘れていたことにつき、大変申し訳ありませんでした。

回答No.1

syuyama

仮にE1に「1:00」、F1に「8:00」と入力します。
C1には
=IF(D1>0,F1,B1-A1-E1)
でいいでしょう。
D1には
=IF(B1-A1-E1<F1,0,B1-A1-E1-F1)
で、いいでしょう。

ですが、これだと10時間働いた場合、C1には通常勤務時間の最大である「8:00」と表示されます。

C1には普通にその場合であっても「10:00」と表示させたいのであれば、C1には
=B1-A1-E1
で、いいでしょう。

お礼

早速の回答有難う御座います。

この式で計算しましたが、午後からの出勤の場合でも-1が適用されていますので、残念ながら計算が合いません。

D1はC1が8時間以上適用ですので良いかと思います。